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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nattran
Helper I
Helper I

Highlight Max value in a matrix

Hi,

 

I've created a measure Avg Sales and below is the matrix I displayed the data:

nattran_0-1595493899673.png

 

Now Im trying to using the conditional formatting to highlight the max value per Department per Level. So the expected result is the following should be highlighted:

 

nattran_1-1595494007016.png

 

Please can someone help?

 

thanks

 

2 ACCEPTED SOLUTIONS

@nattran, well it's DAX you're asking for.

Haven't really tested, but based on your image something like this should work:

 

maxSum =
VAR m =
    CALCULATE (
        MAXX ( SUMMARIZE ( 'table', 'table'[Dep] )CALCULATE ( SUM ( 'table'[Amt] ) ) ),
        REMOVEFILTERS ( 'table'[Dep] )
    )
VAR s =
    SUM ( 'table'[Amt] )
RETURN
    IF ( m s1 )

 

and then conditionally filter SUM('table'[Amt]) when maxSum = 1 then colour.




Feel free to connect with me:
LinkedIn

View solution in original post

v-eachen-msft
Community Support
Community Support

Hi @nattran ,

 

You could set the highlight in conditional formatting with a color measure( [Measure] is the value in matrix ).

FormatMeasure =
VAR a =
    MAXX ( ALLEXCEPT ( 'Table', 'Table'[Column1] ), [Measure] )
RETURN
    IF ( [Measure] = a, "yellow" )

Here is my test result and test file for your reference. 

1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
v-eachen-msft
Community Support
Community Support

Hi @nattran ,

 

You could set the highlight in conditional formatting with a color measure( [Measure] is the value in matrix ).

FormatMeasure =
VAR a =
    MAXX ( ALLEXCEPT ( 'Table', 'Table'[Column1] ), [Measure] )
RETURN
    IF ( [Measure] = a, "yellow" )

Here is my test result and test file for your reference. 

1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Pragati11
Super User
Super User

Hi @nattran ,

 

There is an existing tread for this issue:

https://community.powerbi.com/t5/Desktop/highlight-specific-rows-in-matrix/m-p/893600

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks. But in this case, I need to find dynamic MAX sales value per department per level based on the time period selection. How can I achieve that?

 

thanks 

@nattran, well it's DAX you're asking for.

Haven't really tested, but based on your image something like this should work:

 

maxSum =
VAR m =
    CALCULATE (
        MAXX ( SUMMARIZE ( 'table', 'table'[Dep] )CALCULATE ( SUM ( 'table'[Amt] ) ) ),
        REMOVEFILTERS ( 'table'[Dep] )
    )
VAR s =
    SUM ( 'table'[Amt] )
RETURN
    IF ( m s1 )

 

and then conditionally filter SUM('table'[Amt]) when maxSum = 1 then colour.




Feel free to connect with me:
LinkedIn

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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