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:
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.
I appreciate it very much if anyone can point out what was wrong with my code.
Solved! Go to Solution.
@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"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
User | Count |
---|---|
364 | |
104 | |
64 | |
51 | |
49 |
User | Count |
---|---|
337 | |
121 | |
83 | |
68 | |
62 |