Matrix Measure Total Triple Threat Rock & Roll 04-27-2020 09:58 AM

[Greg_Deckler]

While it would be a bit presumptuous to presume that any single formula could account for all possible measure total situations, the following pattern is presented for handling matrix measure values as well as subtotals and grand totals. This pattern has the added flexibility of being able to handle the same or different aggregation calculations at all three levels. In the example provided, the "normal" aggregation is MIN while at the the subtotal level it is the AVERAGE of those MIN values. At the grand total level, it is the MAX of the AVERAGE of the subtotals.

Overall, this pattern provides extreme flexibility and can be extended to any number of level subtotals.

The main assumption is that there is a "normal aggregation" measure that one is wishing to display in a matrix with correct subtotals and grand total. In this example, the formula for this normal aggregation is "Normal Aggregation = MIN('Table'[Occupancy %]).

MM3TR&R = 
VAR __Category1 = MAX([Category1])
VAR __tmpTable =  SUMMARIZE(
                      ALLSELECTED('Table'),
                      'Table'[Category1],
                      'Table'[Category2],
                      "Aggregation",[Normal Aggregation]
                  )
VAR __SubTotal =  
    AVERAGEX(
        FILTER(
            __tmpTable,
            'Table'[Category1]=__Category1
        ),
        [Aggregation]
    )
VAR __GrandTotal =  
            MAXX(
                GROUPBY(
                    __tmpTable,
                    [Category1],
                    "GTAggregation",
                    AVERAGEX(CURRENTGROUP(),[Aggregation])
                ),
                [GTAggregation]
            )
RETURN IF(
            HASONEVALUE('Table'[Category1]) && HASONEVALUE('Table'[Category2]),
            [Normal Aggregation],
            IF(HASONEVALUE('Table'[Category1]),
                __SubTotal,
                __GrandTotal
            )
        )

eyJrIjoiNzMyYTYwY2QtMGVlMC00MjdmLWIwNDUtYmQ1ZDFjNWZiM2E5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[JB_AT]

@Greg_Deckler 

In my situation, I have 10 hierarchy rows from the same table, I need a subtotal at each level that is correct, then the subtotal from the lower level is added to the subtotal of the next level up. I can't seem to achve this. 

How can i adapt your measure to do thsi?

Thanks

[luzsoulez]

Hi! the matrix with numbers is working fine, but when I try to present it as % the values come wrong because it is calculating based on the last value instead of period over period. how can I solve this? It is using 487 total from period 24 to calculate all the periods %

[Anonymous]

Do the formula work between two related tables?

Thanks for your help

[TheCAKurtle]

@Greg_Deckler 

Does this work for 2 fact tables that have 2 different dimensions connected to both? 
So in my case the UWI column in BLOPS Dim is Category 1 and HierarchyCC column in Hierarchy Cost Centers is Category 2. 

Thank you for the informative post!!