Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey all,
I've been working on this problem for way too long and maybe I'm overthinking it, but I can't get this calculation to work the way I feel it should. I have data as shown below. To boil it down to the important points for this calculation, the tables are:
Dim_Model: by SKU with Category, Group, Manufacturer
Dim_Calendar: by Date with SeasonYear
Dim_Geography: by State
Fact_Inventory: by SKU, Date, State with Sales
The resulting visual that I am trying to accomplish is a Matrix with various measures (not important to this question) and a percent to total type calculation (the crux of this question) that aggregates to a level not shown in the visual. To further complicate things, there are of course slicers on the report to consider. Any help is greatly appreciated as I'm sure this is something simple I'm overlooking.
Filters: Dim_Model.Manufacturer = "A"
Slicers: Dim_Model.Category, Dim_Calendar.SeasonYear, and Dim_Geography.State
Desired measure: Percent of sales for that SKU's/Category's (whatever drill level the user has it at) Group vs the total sales of that Group for all Manufacturers
Considerations: All manufacturers will use the same Group names for industry data purposes but none use the same Category nor SKU nomenclature, the users want a matrix so they can drill down/up between Category and SKU but only using the top buttons not the tree expanders/contractors, I realize this measure duplicates some data on the visual but it's helpful enough that it's fine until we can get more granular Category and SKU comparisons
Example data: here it's clear that Group is Outdoor, Indoor, and Multi, though assume it really cannot be inferred by the Category name, you must use the Group field
Category | Sales | Group Sales | Group Sales of Industry | % of Industry |
Outdoor - Large | $ 152,254 | $ 228,038 | $ 272,341 | 83.7% |
Outdoor - Small | $ 75,784 | $ 228,038 | $ 272,341 | 83.7% |
Indoor - Large | $ 12,347 | $ 24,887 | $ 786,574 | 3.2% |
Indoor - Medium | $ 9,087 | $ 24,887 | $ 786,574 | 3.2% |
Indoor - Small | $ 3,453 | $ 24,887 | $ 786,574 | 3.2% |
Multi - Small | $ 111,980 | $ 1,112,320 | $ 3,547,908 | 31.4% |
Multi - X Small | $ 1,000,340 | $ 1,112,320 | $ 3,547,908 | 31.4% |
apologies for the smashed right-most numbers, it keeps telling me invalid HTML when I right-align even though I'm only using the built-in buttons
Solved! Go to Solution.
Hey @PaulDBrown ! Thanks again for this. I worked through some iterations of what you suggested and it still gave me only the numbers for items visible.
Buuuuuut, I was able to figure it out! 🙌 Through this rather inelegant measure, I got the exact numbers I need at any level of visible hierarchy
This should work:
Sales all manufacturers & Category Group= CALCULATE([Sum of Sales], ALL(Dim_Model.Manufacturer), ALL(Dim_Model.Category Group))
% over all manufacturers = DIVIDE([Sum of Sales], [Sales all manufacturers & Category Group])
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ! Thanks so much for the response. Category and Group are two separate fields; Category being one of the two possibly visible (SKU the other) in the Rows of the Matrix and Group being the non-visible field that I need to aggregate to. I tried the ALL(Dim_Model.Manufacturer) and added the second ALL() thusly:
Ok, so to achieve what you need, given that Category and Group are separate fields, you can either:
1) add both fields to the visual and use the meaure
2) create a new field (calculated column) in the dim table for category - group. You can then use this field in the measure instead of the ALL(Dim_Model.Group) expression. Ie. Use ALL(Dim_Model. Category - Group)) and rename it in the visual.
make sense?
Proud to be a Super User!
Paul on Linkedin.
Hey @PaulDBrown ! Thanks again for this. I worked through some iterations of what you suggested and it still gave me only the numbers for items visible.
Buuuuuut, I was able to figure it out! 🙌 Through this rather inelegant measure, I got the exact numbers I need at any level of visible hierarchy
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |