Minimum value of each group in a hierarchy in matrix visual

[Anonymous]

I've a dataset which looks as mentioned below: 

Level_1	Level_2	Value
A	A_1	1
A	A_1	2
A	A_2	3
A	A_2	4
A	A_2	5
B	B_1	10
B	B_1	11
B	B_1	12
B	B_1	13
B	B_2	14
B	B_2	15
B	B_3	16
B	B_3	17
B	B_3	18

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: 

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:

I need the output as 3 instead.

Any help will be very much appreciated. 

[Mariusz · Accepted solution]

Hi @Anonymous 

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.

[Mehul]

Hi @Mariusz 

Is it possible to filter on another column.  I have similar set of data where I'm looking to get min % value and roll it up to higher level.  In my case I have the seperate level colums and % value but also location.

1.1.2. 50%

1.1.2.1 0 %

1.1.2.2 33%

1.1.2.3. 38%

In my case above, the rollup should be 0 %. 

[Anonymous]

Thanks a lot @Mariusz , solution worked perfectly fine.