topic Re: DAX Conditional Formatting the maximum value for each column in a Matrix in DAX Commands and Tips

DAX Conditional Formatting the maximum value for each column in a Matrix

sscanlon — Sat, 08 Jan 2022 19:22:56 GMT

Hi, I followed this post and video but cannot figure out a few things, hoping someone can help me figure it out.

https://www.sqlbi.com/articles/highlighting-the-minimum-and-maximum-values-in-a-power-bi-matrix/

Essentially, I want to do what they highlight here, but for the GP column for each of these (Cloud / Network / Security) in my image from my matrix visual.

All of the data is in a single table 'SAP-DIRECT', and the rows are [EAM] and Columns are [GROUP]

I am trying to show a Star Icon for largest GP, and would be a bonus to show another icon for the 2nd largest GP but not totally needed.

My current measure is below, I use that for conditional formatting - Values Only - based on the measure, if = 1 etc.

One thing I have never seen explained when people show this method, what is the [Sales Amount] Measure that is called in the link above?

I created one called [GPmeasure] and just use Sum for my GP value, but have no idea if that is right.

Currently this is what shows in the visual, no icon on any value.

My current measure:

GP LARGEST SAP-DIRECT =
VAR Vals =
CALCULATETABLE(
ADDCOLUMNS (
SUMMARIZE ( 'SAP-DIRECT', 'SAP-DIRECT'[EAM], 'SAP-DIRECT'[GROUP] ), -- I believe I have this correct? Row first then Column?
"@GPMAX", [GPmeasure] -- This is my measure I created, see below, not sure if it is correct?
),
ALLSELECTED ()
)
VAR MinValue = MINX ( Vals, [@GPMAX] )
VAR MaxValue = MAXX ( Vals, [@GPMAX] )
VAR CurrentValue = [GPmeasure]
VAR Result =
SWITCH (
TRUE,
CurrentValue = MinValue, 1, -- 1 for MIN
CurrentValue = MaxValue, 2 -- 2 for MAX
)
RETURN
Result

My measure that is called by the above:

GPmeasure = CALCULATE(SUM('SAP-DIRECT'[GP]))

again have no idea if this is right, or needs to be SUMX?

My Conditional formatting - fairly certain this is correct

Thank you so much for any help!

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

lbendlin — Sun, 09 Jan 2022 17:50:21 GMT

This seems to be generally going in the right direction. Keep in mind that your measure is computed in each of the individual contexts. Do you want the star once for the whole table, or once for each column?

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

sscanlon — Sun, 09 Jan 2022 18:49:40 GMT

Hi, thank you, here is an idea of the data inside the table.

I would like to just show an icon once for each column (which in this case would be once for each [GROUP]

EAM	REVENUE	GP	GROUP
John	$275	$44	☁️ Cloud
Larry	$51,556	$5,402	☁️ Cloud
Karl	$34,048	$5,059	☁️ Cloud
Kathy	$6,514	$850	☁️ Cloud
Tom	$38,528	$6,945	☁️ Cloud
John	$31,557	($4,097)	🌐 Network
Larry	$3,500	$301	🌐 Network
Karl	$152,191	$26,233	🌐 Network
Kathy	$131,786	$16,606	🌐 Network
Tom	$22,232	$1,795	🌐 Network
John	$270,091	$21,379	🔒 Security
Karl	$42,710	$4,271	🔒 Security
Kathy	$31,657	$4,605	🔒 Security

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

lbendlin — Mon, 10 Jan 2022 01:05:11 GMT

Like this?

Of course the proper way would be to use RANKX() etc but that gets a little tricky with measures. You would also need to decide what to do with ties etc.

Sample pbix attached.

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

sscanlon — Mon, 10 Jan 2022 01:24:32 GMT

Thank you so much for taking the time to do that, it looks perfect, but only shows zeros on every row in the column when I drag the measure into the visual (to test and see if I get 0, 1 or 2).

Not sure what else I am doing wrong, I even cleared any filters on the visual or page etc.

Again thank you!

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

lbendlin — Mon, 10 Jan 2022 03:30:16 GMT

Please provide sanitized sample data that fully covers your issue.. Please show the expected outcome.

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

sscanlon — Mon, 10 Jan 2022 05:09:21 GMT

I am thinking it is because of our Invoice column, every Invoice is a row, and they can be + GP or - GP, let's say a credit for negative GP.

Issue is we do not put EAM name for negative GP entries / rows. So I think this is throwing off the calculation of GP and the measure.

Might be too hard to figure out, I can try to scrub some data and send to you, maybe some example rows of + and - GP.

Can do tomorrow if you think that helps?

Thanks again

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

sscanlon — Tue, 11 Jan 2022 01:22:03 GMT

After a little more digging, it seems like it would work if I could SUM the GP total for each EAM by GROUP.

I expanded the table more below and did not realize that each INVOICE has a unique GP, sometimes on the same date and sometimes not.

I believe the measure is grabbing the first max value and not the SUM of each EAM?

Thank you for any thoughts, I looked and it seems MAXX allows for SUM but MAX does not, I could not even call a SUM Measure with MAX, needs to be a column.

DATE	INVOICE	EAM	REVENUE	GP	GROUP
12/15/2021	10006009	Dale	$275	$44	☁️ Cloud
12/15/2021	10006010	Harry	$40,299	$3,929	☁️ Cloud
12/15/2021	10006011	Harry	$51,556	$5,402	☁️ Cloud
12/15/2021	10006012	Karl	$34,048	$5,059	☁️ Cloud
12/15/2021	10006013	Kathy	$6,514	$850	☁️ Cloud

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

lbendlin — Tue, 11 Jan 2022 01:41:39 GMT

Read about the difference between aggregators (SUM, MAX) and iterators (SUMX, MAXX). That will help you to decide when to apply which (very roughly aggregators work on columns while iterators work on rows).

Let me know when you have sorted your actual requirement out. The measure I provided is only one of many possible implementations (and a very simplistic one, too). It did work for your original request, but it's perfectly fine for requirements to change during prototyping.

Re: DAX Conditional Formatting the maximum value for each column in a Matrix

sscanlon — Tue, 11 Jan 2022 19:33:19 GMT

I ended up using using a Reference of original SAP-DIRECT Table, then filtered down dates I wanted and used "Group By" in Power Query Editor, once I did that for EAM and GROUP, then I could Sum new columns by REVENUE and GP and that gave me the exact table I wanted.

From there your measure worked great. I could not figure out how to use MAX Aggregate with SUM of REV / GP, read everything I could find and my brain could not do it.

Anyway thank you for the guidance, it worked in the end!