cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
swapnil_singhal Frequent Visitor
Frequent Visitor

Minimum value of each group in a hierarchy in matrix visual

I've a dataset which looks as mentioned below: 

 

 

Level_1Level_2Value
AA_11
AA_12
AA_23
AA_24
AA_25
BB_110
BB_111
BB_112
BB_113
BB_214
BB_215
BB_316
BB_317
BB_318

 

I want use the matrix visualization to aggregate the data and select the sum of minimum value at at each hierarchy level. Refer to screen shots below: 

Level_1.png

 

The matrix visual shows the minimum value which is at next level of hierarchy, but I need the outcome at "15" which is minimum out of 15 and 126. Similarly if we go a level down:

Level_2.png

 

I need the output as 3 instead.

 

Any help will be very much appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
Mariusz Senior Member
Senior Member

Re: Minimum value of each group in a hierarchy in matrix visual

Hi @swapnil_singhal 

You can try the below DAX expression 

Min of Values = 
VAR levelOne = 
    MINX(
        ALL( YourTable[Level_1] ), 
        CALCULATE( SUM( YourTable[Value] ) )
    )
VAR levelTwo =
    MINX(
        ALL( YourTable[Level_2] ),
        CALCULATE( SUM( YourTable[Value] ) )
    )
RETURN 
SWITCH(
    TRUE, 
    HASONEVALUE( YourTable[Level_2] ), levelTwo,
    HASONEVALUE( YourTable[Level_1] ), levelOne,
    SUM( YourTable[Value] ) 
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



2 REPLIES 2
Mariusz Senior Member
Senior Member

Re: Minimum value of each group in a hierarchy in matrix visual

Hi @swapnil_singhal 

You can try the below DAX expression 

Min of Values = 
VAR levelOne = 
    MINX(
        ALL( YourTable[Level_1] ), 
        CALCULATE( SUM( YourTable[Value] ) )
    )
VAR levelTwo =
    MINX(
        ALL( YourTable[Level_2] ),
        CALCULATE( SUM( YourTable[Value] ) )
    )
RETURN 
SWITCH(
    TRUE, 
    HASONEVALUE( YourTable[Level_2] ), levelTwo,
    HASONEVALUE( YourTable[Level_1] ), levelOne,
    SUM( YourTable[Value] ) 
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



swapnil_singhal Frequent Visitor
Frequent Visitor

Re: Minimum value of each group in a hierarchy in matrix visual

Thanks a lot @Mariusz , solution worked perfectly fine.