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
dididing2001
Frequent Visitor

Highlight max value for each row in a matrix

I am trying to create a Power BI matrix and highlight the max value of each row to show the peak hour. 

Here is the matrix I made and the related fields:

dididing2001_3-1634492657747.png

dididing2001_1-1634492549093.png

dididing2001_2-1634492580376.png

I made a measure to find the max value for each day by modifying the original one of Alberto Ferrari (https://www.sqlbi.com/articles/highlighting-the-minimum-and-maximum-values-in-a-power-bi-matrix/)

My code is:

max_highlight = 
VAR Vals =
    CALCULATETABLE(
        ADDCOLUMNS (
            SUMMARIZE ( 'pickup_dropoff', 'pickup_dropoff'[DayName], 'pickup_dropoff'[hour] ),
            "PeakHour", SUMX (pickup_dropoff, 'pickup_dropoff'[frequency] )
        ),
        ALLSELECTED ('pickup_dropoff'[DayName])
    )

VAR MaxValue = MAXX ( Vals, [PeakHour] )
VAR CurrentValue = SUMX('pickup_dropoff', 'pickup_dropoff'[frequency] )
VAR Result =
    SWITCH (
        TRUE,
        CurrentValue = MaxValue, 2 -- 2 for MAX
    )
RETURN
    Result

I expected the max value would be colored red. For example, for Sunday, the number 424014 will be highlighted. For Monday, it will be 479349, and so on.

However, what I received after conditional formatting is a red matrix - all values were highlighted.

dididing2001_4-1634493104180.png

I appreciate it very much if anyone can point out what was wrong with my code.

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@dididing2001 

I create a measure for the conditional Formatting as follows, modify your table and column names as per your model. I also attached the file below my signature.

MaxInRow = 
VAR __MaxUnits = 
MAXX(
    ALLSELECTED( Data[Month Number] ),
    [Total Units]
)
RETURN
IF( 
    [Total Units] = __MaxUnits,
    "RED"
)

 

Fowmy_0-1634494851440.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@dididing2001 

I create a measure for the conditional Formatting as follows, modify your table and column names as per your model. I also attached the file below my signature.

MaxInRow = 
VAR __MaxUnits = 
MAXX(
    ALLSELECTED( Data[Month Number] ),
    [Total Units]
)
RETURN
IF( 
    [Total Units] = __MaxUnits,
    "RED"
)

 

Fowmy_0-1634494851440.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

This is very helpful I have used this for one of my matrix's. What if you add one more layer to this problem. For each  item that you want to identify the max value of, there is a layer above that? 
Example. 

Stetty_0-1704307586175.png

I have a switch true measure that allows me to have multiple measures in a matrix. I have a power bi that would put two teams as a matchup, it would then show the dates of the last times they have competed. 

I can use your method when Date is not in this equation and you are only looking strictly at two teams. But once you put date above it in a matrix it will not come in correctly. Your formula would read something like this. How would i write it so it looks at not just teams but isolates the specific dates when they competed too?

VAR __MaxUnits = 
MAXX(
    ALLSELECTED( team ),
    [Switch True Measure]
)
RETURN
IF( 
    [Switch True Measure] = __MaxUnits,
    "RED"
)

 

Hi - I am trying to solve a very simliar problem, but my date (which I represent as a year) comes from a date table separate from my table with my data. I can't figure out what I am doing wrong here.  Can you help me figure out how to write this? 


@Fowmy Your solution works like a charm! Thank you so much.

The measure works fine, and when I put the week filter on, the highlight also changes. This is definitely what I'm looking for.

dididing2001_0-1634496030872.png

 

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.