Forum Discussion
Helper IIDAX Conditional Formatting the maximum value for each column in a Matrix
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.
Super UserLike 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.
Helper IIThank 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!
- lbendlin
Please provide sanitized sample data that fully covers your issue.. Please show the expected outcome.
- sscanlon
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
- sscanlon
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 - lbendlin
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.