Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paulevans85
New Member

CALCULATE to a different level of detail in a matrix than is shown

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

paulevans85_1-1611123977195.png

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

CategorySalesGroup SalesGroup Sales
of Industry
% of
Industry
Outdoor - Large $     152,254 $     228,038 $     272,34183.7%
Outdoor - Small $        75,784 $     228,038 $     272,34183.7%
Indoor - Large $        12,347 $       24,887 $     786,5743.2%
Indoor - Medium $          9,087 $       24,887 $     786,5743.2%
Indoor - Small $          3,453 $       24,887 $     786,5743.2%
Multi - Small $     111,980 $ 1,112,320 $  3,547,90831.4%
Multi - X Small $  1,000,340 $ 1,112,320 $  3,547,90831.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

1 ACCEPTED 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

Sales At Group =
VAR grp = SELECTEDVALUE(Dim_Model[Group])

RETURN
    CALCULATE(
        IF(
            ISBLANK(grp)
            , SUM(Fact_Inventory[Sales])
            , CALCULATE(
                SUM(Fact_Inventory[Sales])
                , Dim_Model[Group] = grp
            )
        )
        , ALL(Dim_Model[SKU])
        , ALL(Dim_Model[Category])
    )
 
The ISBLANK(grp) conditional is for situations where more than 1 Group is "selected", which only happens (in my report) for grand totals and therefore needs to just be all Sales (contingent on other slicers on the page)

View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

@paulevans85 

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])





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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:

  1. as you've written it - doesn't work because they're separate fields
  2. just Category - gives the total industry sales ($1,365,245 from table above) for all Groups for each row (currently shown at Category level of hierarchy) and now shows records for other Categories not part of Manufacturer "A" with blanks for other measures and the same total for this measure
    1. To get rid of the extra rows, I created a measure Sales_All = CALCULATE(SUM(Fact_Inventory.Sales), ALL(Dim_Model.Manufacturer)) and filtered to "is not blank" on the visual. This brought it back to just the matrix as described above with a different [Sales all manufacturers & Category Group] for each, but aggregating to that Category level. The grand total in the very bottom is the correct grand total, but the individual subtotals do not add up to that grand total because the other Sales (not Manufacturer "A") are not visible
  3. just Group - doesn't add extra rows and does show the correct grand total, but shows individual subtotals by Category rather than subtotals for Group
    1. Added the [Sales_All] filter again for testing and it changed only the grand total and makes it the sum of the shown subtotals
  4. Category and Group - same as 2 and 2.1 above

@paulevans85

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?

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

Sales At Group =
VAR grp = SELECTEDVALUE(Dim_Model[Group])

RETURN
    CALCULATE(
        IF(
            ISBLANK(grp)
            , SUM(Fact_Inventory[Sales])
            , CALCULATE(
                SUM(Fact_Inventory[Sales])
                , Dim_Model[Group] = grp
            )
        )
        , ALL(Dim_Model[SKU])
        , ALL(Dim_Model[Category])
    )
 
The ISBLANK(grp) conditional is for situations where more than 1 Group is "selected", which only happens (in my report) for grand totals and therefore needs to just be all Sales (contingent on other slicers on the page)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.