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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!
User | Count |
---|---|
191 | |
68 | |
63 | |
56 | |
52 |
User | Count |
---|---|
251 | |
207 | |
102 | |
70 | |
70 |