Maestro Chip
New Member
Hi All! I'm trying to tackle an issue in creating a Pivot output from TWO tables (Sales Table & Space Table) where each table has values that don't have a corresponding value in the other table.
The idea is to merge two tables together in Query - and end goal is to have calculate "Space Efficiency" (sum of Sales / Space) for each permutation of Date/Sales/Store Name/Product Type/Product Category/Product Brand (even when some values are not available).
Requirements of the output Pivot is :
1) All Space should always be shown regardless of availability of Sales (eg. Store B should always show Apparel>Bottoms>Nike space as 40 SqM even if there is zero Sales in this item on a particular day. The new Space Efficiency column can show 0 or an error - this is okay)
2) The pivot should correctly display the total Space of any combination of Filters (eg. I can see the total space of Store A as a whole / with a breakdown of each Product Type / or all the way to a breakdown of Product Category & Product Brand). This will also allow the Space Efficiency to be calculated correctly even if across multiple or single dates (Eg. Space Efficiency of Store A using Sales of 1st & 2nd July together)
Table as follows :
1. Sales Table (Highlighted in Red is entry that has no Space data in second table):
2. Space Table (Highlighted in Red is entry that has no Sales data in first table) :
Issues faced :
Issue #1 : Since there are products not available both ways (either not in Sales or not in Space), I can't efficiently merge the two tables together whilst keeping all product from both tables visible. The only approach I know is to create a distinct permutation of Date + Store Name + Products and merging the Sales and Space data to this new 'Distinct' mega list. (The actual Sales table has up to a year of data with a larger list of stores, categories and brands).
Issue #2 : (Assuming #1 is resolved) I will continue to create a measure with Power Pivot to calculate the 'space efficiency' (Sum of Sales / XX of Space) which I can then filter through the description or only a part of it (eg. Space Efficiency of Apparel as a whole in Store A). I'm not sure how to do this with the "Space" value since it should remain constant across all dates instead of being summed up like Sales. (eg. The space of 'Store B-Footwear-Closed Shoe-Adidas' should always show 25 SqM even if I look through a whole month of data in one line).
What I have tried :
Issue #1
- Creating a Left Outer join will not capture the RED entry (depending on which table I start the merge with) since some data is missing from the opposing table
- A Full Outer join starting with Sales table will capture all points, but, if I expand Space values, the corresponding Product descriptions will be blank. If I expand all headers, then I will have double columns for each Product description column - with entries sitting in either one side or the other or both.
- A Left Outer join appended with a Right Anti join will work to capture all data together EXCEPT the date value will be missing in some entries since the Space value won't be mapped to each instance of Date
-I will resort to my 'Distinct' mega list if there is no solution (Though the refresh takes much too long - and I wonder if a more efficient approach is possible)
Issue #2
- In calculating the 'Space Efficiency' (Sales/Space) Sales can be captured with a measure using Sum of Sales but I'm stuck on how to capture the Space value correctly - since it needs to be constant across every date (or a collection of dates). Sum of Sales / Average of Space will work when all Product headers are expanded but if I collapse a Header, then it averages between two different values.
It's a long post - and I tried to make it as clear as I can (hopefully) any help would be appreciated and will gladly share more details if needed.
The idea is to merge two tables together in Query - and end goal is to have calculate "Space Efficiency" (sum of Sales / Space) for each permutation of Date/Sales/Store Name/Product Type/Product Category/Product Brand (even when some values are not available).
Requirements of the output Pivot is :
1) All Space should always be shown regardless of availability of Sales (eg. Store B should always show Apparel>Bottoms>Nike space as 40 SqM even if there is zero Sales in this item on a particular day. The new Space Efficiency column can show 0 or an error - this is okay)
2) The pivot should correctly display the total Space of any combination of Filters (eg. I can see the total space of Store A as a whole / with a breakdown of each Product Type / or all the way to a breakdown of Product Category & Product Brand). This will also allow the Space Efficiency to be calculated correctly even if across multiple or single dates (Eg. Space Efficiency of Store A using Sales of 1st & 2nd July together)
Table as follows :
1. Sales Table (Highlighted in Red is entry that has no Space data in second table):
| Date | Store Name | Product Type | Product Category | Product Brand | Sales |
|---|---|---|---|---|---|
01/07/2024 | Store A | Apparel | Tops | Nike | 500 |
02/07/2024 | Store A | Apparel | Tops | Adidas | 450 |
03/07/2024 | Store A | Apparel | Tops | Nike | 700 |
01/07/2024 | Store A | Footwear | Open Shoe | Nike | 200 |
02/07/2024 | Store A | Footwear | Open Shoe | Adidas | 280 |
03/07/2024 | Store A | Footwear | Open Shoe | Nike | 300 |
01/07/2024 | Store B | Apparel | Bottoms | Nike | 400 |
02/07/2024 | Store B | Apparel | Bottoms | Adidas | 550 |
03/07/2024 | Store B | Apparel | Bottoms | Nike | 600 |
02/07/2024 | Store B | Footwear | Closed Shoe | Adidas | 320 |
2. Space Table (Highlighted in Red is entry that has no Sales data in first table) :
| Store Name | Product Type | Product Category | Product Brand | Space (Meter Square) |
|---|---|---|---|---|
| Store A | Apparel | Tops | Nike | 50 |
| Store A | Footwear | Open Shoe | Nike | 20 |
| Store A | Footwear | Open Shoe | Adidas | 30 |
| Store B | Apparel | Bottoms | Nike | 40 |
| Store B | Apparel | Bottoms | Adidas | 55 |
| Store B | Footwear | Closed Shoe | Adidas | 25 |
| Store B | Footwear | Closed Shoe | Nike | 22 |
Issues faced :
Issue #1 : Since there are products not available both ways (either not in Sales or not in Space), I can't efficiently merge the two tables together whilst keeping all product from both tables visible. The only approach I know is to create a distinct permutation of Date + Store Name + Products and merging the Sales and Space data to this new 'Distinct' mega list. (The actual Sales table has up to a year of data with a larger list of stores, categories and brands).
Issue #2 : (Assuming #1 is resolved) I will continue to create a measure with Power Pivot to calculate the 'space efficiency' (Sum of Sales / XX of Space) which I can then filter through the description or only a part of it (eg. Space Efficiency of Apparel as a whole in Store A). I'm not sure how to do this with the "Space" value since it should remain constant across all dates instead of being summed up like Sales. (eg. The space of 'Store B-Footwear-Closed Shoe-Adidas' should always show 25 SqM even if I look through a whole month of data in one line).
What I have tried :
Issue #1
- Creating a Left Outer join will not capture the RED entry (depending on which table I start the merge with) since some data is missing from the opposing table
- A Full Outer join starting with Sales table will capture all points, but, if I expand Space values, the corresponding Product descriptions will be blank. If I expand all headers, then I will have double columns for each Product description column - with entries sitting in either one side or the other or both.
- A Left Outer join appended with a Right Anti join will work to capture all data together EXCEPT the date value will be missing in some entries since the Space value won't be mapped to each instance of Date
-I will resort to my 'Distinct' mega list if there is no solution (Though the refresh takes much too long - and I wonder if a more efficient approach is possible)
Issue #2
- In calculating the 'Space Efficiency' (Sales/Space) Sales can be captured with a measure using Sum of Sales but I'm stuck on how to capture the Space value correctly - since it needs to be constant across every date (or a collection of dates). Sum of Sales / Average of Space will work when all Product headers are expanded but if I collapse a Header, then it averages between two different values.
It's a long post - and I tried to make it as clear as I can (hopefully) any help would be appreciated and will gladly share more details if needed.