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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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
Mariusz
Community Champion
Community Champion

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.
Mariusz Repczynski



View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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.
Mariusz Repczynski



Mehul
Frequent Visitor

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
Not applicable

Thanks a lot @Mariusz , solution worked perfectly fine. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.