March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
Hi,
I've created a measure Avg Sales and below is the matrix I displayed the data:
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:
Please can someone help?
thanks
Solved! Go to Solution.
@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 = s, 1 )
and then conditionally filter SUM('table'[Amt]) when maxSum = 1 then colour.
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.
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.
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
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 = s, 1 )
and then conditionally filter SUM('table'[Amt]) when maxSum = 1 then colour.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
55 | |
27 | |
17 | |
13 |