Aggregating matrix on level in the hierarchy

[Anonymous]

Hello, 

I'm having some trouble getting the intended I wish. In the table below, The green level is calculated using the values of the blue rectangles. However, for the visualization to make sense It would need to take on the average of the hierarchy right below, the orange circles. 

Basically I'd like that the average status of a level would take into account the average of the level right after in the hierarchy, instead of the values in the most granular level. 

Thanks,

Pedro Caldeira

[v-yiruan-msft]

Hi @Anonymous,

Could you please provide some sample data and your desired result? For example, you have the table in the below screen shot. What's the correct value for D1 and D2 with the red circle? Please provide the calculation logic or formula. Like D1=(E1+E2)/2?

Best Regards

Rena 

[Anonymous]

Hello @v-yiruan-msft ,

That is exactly the calculation we wish. For D1 the value would be (E1+E2)/2=(13.00+9.00)/2 = 11. For D2 the value would be (E3 + E4)/2  = (19.50 + 20)/2 = 19.75, which isn't the result that was calculated (19.67). 

The idea would also be to let the user skip a level. For instance, skipping level C, the the Calculation for C1 would be the average of E1 and E2, and For C2 would be the average of E3 and E4.

For a first instance, we would like to know how to achieve the calculations given in the first paragraph (D2=(E3 + E4)/2  = (19.50 + 20)/2 = 19.75 and D1 = (E1+E2)/2=(13.00+9.00)/2 = 11)

Thank you,

Best Regards,

Pedro

[v-yiruan-msft]

Hi @Anonymous ,

I created a sample pbix file base on your requirement, please check if that is what you want. The following 2 measures has been created in the file.

Average of level 5 = 
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Level] = MAX ( 'Table'[Level] )
            && 'Table'[Level 1] = MAX ( 'Table'[Level 1] )
            && 'Table'[Level 2] = MAX ( 'Table'[Level 2] )
            && 'Table'[Level 3] = MAX ( 'Table'[Level 3] )
            && 'Table'[Level 4] = MAX ( 'Table'[Level 4] )
    )
)

Average value = 
VAR _avgValue =
    CALCULATE ( AVERAGE ( 'Table'[Value] ) )
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Level],
        'Table'[Level 1],
        'Table'[Level 2],
        'Table'[Level 3],
        "avgofl4", SUMX ( VALUES ( 'Table'[Level 4] ), [Average of level 5] )
    )
VAR _sumofAvg =
    MAXX ( _tab, [avgofl4] )
VAR _countoflvl4 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Level 4] ),
        FILTER (
            'Table',
            'Table'[Level] = MAX ( 'Table'[Level] )
                && 'Table'[Level 1] = MAX ( 'Table'[Level 1] )
                && 'Table'[Level 2] = MAX ( 'Table'[Level 2] )
                && 'Table'[Level 3] = MAX ( 'Table'[Level 3] )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Level 3] ) && NOT ( ISFILTERED ( 'Table'[Level 4] ) ),
        DIVIDE ( _sumofAvg, _countoflvl4 ),
        _avgValue
    )

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft,

Thank you for your prompt response,

This did achieve the intended value we wish for , for the one exact level in the hierarchy I presented, not for the whole hierarchy.

The higher levels, will continue to show the incorrect value (which is still based on the lowest hierarchy.

In the mockup data, as an example, we wish that the value for A is (B1 +B2)/2 = (23.8 + 21.75)/2 ~= 22,78 and not A=22,89  which comes from SUM (Fi={1,2,3,4,5,6,7,8,9})/Count(Fi={1,2,3,4,5,6,7,8,9}) = (31 + 17 +19 + 27 + 25 + 22 + 25 + 20 + 20) / 9 ~=22,89

Following the pattern you provided I believe we can hardcode the calculation for the correct value for each higher levels but I was wondering if there is a more elegant way of generalizing this calculation for all levels within the context. With this I mean:

Ei = SUM (F Levels in Ei)/ COUNT (F Levels in Ei),

Di = SUM (E Levels in Di)/ COUNT (E Levels in Di),

Ci = SUM (D Levels in Ci)/ COUNT (D Levels in Ci), all the way up to A

Best regards,

Pedro

[amitchandak]

@Anonymous , Not very clear. But if you want the subtotal to different from what automatically comes you can use isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Greg_Deckler]

@Anonymous I think you want MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150