Forum Discussion

sscanlon's avatar
sscanlon
Icon for Helper II rankHelper 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-powe...
Help Requested
  • lbendlin's avatar
    lbendlin
    4 years ago

    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.

sscanlon's avatar
sscanlon
Icon for Helper II rankHelper II
4 years ago

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. 

 

DATEINVOICEEAMREVENUEGPGROUP
12/15/202110006009Dale$275$44 ☁️ Cloud
12/15/202110006010Harry$40,299$3,929 ☁️ Cloud
12/15/202110006011Harry$51,556$5,402 ☁️ Cloud
12/15/202110006012Karl$34,048$5,059 ☁️ Cloud
12/15/202110006013Kathy$6,514$850 ☁️ Cloud
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

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.

  • sscanlon's avatar
    sscanlon
    Icon for Helper II rankHelper II
    4 years ago

    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!