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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sumitnegi1266
Frequent Visitor

How to insert rows in Power BI Matrix to show minimum and maximum values

Hi,

I have below data and need to display extra rows showing minimum and maximum for each set. I am also using company slicer, so if company A and Company B are selected, it should show minimum and maximum as per the data filtered in Power BI matrix.

 

My Data:

sumitnegi1266_0-1693719138202.png

 

Expected Output:

sumitnegi1266_1-1693719158105.png

 

1 ACCEPTED SOLUTION

try this

https://1drv.ms/u/s!AiUZ0Ws7G26Rijc76j-np9aWnkBQ?e=UqwSfU

 

Final = 
VAR _min =
    CALCULATE ( MIN ( 'Table'[Value] ), ALLSELECTED('Table'[Set] ) )
VAR _max =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLSELECTED('Table'[Set] ) )
RETURN
    SWITCH (
        TRUE (),
       SELECTEDVALUE ( DimCompany[Company] ) = "Minimum", _min,
      SELECTEDVALUE ( DimCompany[Company] ) = "Maximum", _max,
        CALCULATE (
            [Sales],
            TREATAS ( VALUES ( DimCompany[Company] ), 'Table'[Company] )
        )
    )

 

 

View solution in original post

7 REPLIES 7
sumitnegi1266
Frequent Visitor

Thank you so much @Ahmedx, it works as expected now 🙂

sumitnegi1266
Frequent Visitor

@AhmedxThanks for sharing the pbix. The only issue with this is, when I select companies under Set 1, it should give the minimum and maximum from those selected companies. In the screenshot below, the minimum for Set 1 should be 10. Also, the min and max should appear only once.

sumitnegi1266_0-1693740762846.png

 

 

@Ahmedx It is still showing incorrect results. For company A and Company B selected, the maximum should be 30, but it is currently showing 50.

sumitnegi1266_0-1693826412885.png

 

everything shows correctly

Screenshot_1.png

try this

https://1drv.ms/u/s!AiUZ0Ws7G26Rijc76j-np9aWnkBQ?e=UqwSfU

 

Final = 
VAR _min =
    CALCULATE ( MIN ( 'Table'[Value] ), ALLSELECTED('Table'[Set] ) )
VAR _max =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLSELECTED('Table'[Set] ) )
RETURN
    SWITCH (
        TRUE (),
       SELECTEDVALUE ( DimCompany[Company] ) = "Minimum", _min,
      SELECTEDVALUE ( DimCompany[Company] ) = "Maximum", _max,
        CALCULATE (
            [Sales],
            TREATAS ( VALUES ( DimCompany[Company] ), 'Table'[Company] )
        )
    )

 

 

Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RijYWQyfB8iSBT4ef?e=XiaJWg

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.