Forum Discussion

sscanlon's avatar
sscanlon
Helper II
4 years ago
Solved

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

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!

 

  • 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.

9 Replies

  • 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. 

    • sscanlon's avatar
      sscanlon
      Helper II

      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]

       

      EAMREVENUEGPGROUP
      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
      • lbendlin's avatar
        lbendlin
        Super User

        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.