Forum Discussion
Conditional Formatting by Row Across Multiple Measures
Hello,
I have a calculated measure for each month of the year in my table and need to figure out a way to apply a conditional formatting across each row. Ideally, I'd like to have a a simple Min/Max gradient from January through December for each project. In other words, for current YTD, Project 19-0001-02 would have strong green in March (Max) and white in February (Min). Here is an example in XLSX:
I just want to replicate it in my table visual in BI and I cannot figure it out. Will it take some complex DAX?:
PS - I'm relatively new to BI, so I'm learning as quickly as I can. Thanks so much!
7 Replies
Hi gowen_WHG ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below and apply conditional formatting for the matrix as below screenshot:
Conditional formmating = VAR _selpro = SELECTEDVALUE ( 'Table'[Project Number] ) VAR _max = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Project Number] = _selpro ), [Sum of sales] ) VAR _min = MINX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Project Number] = _selpro ), [Sum of sales] ) RETURN IF ( [Sum of sales] = _min, "#9bdb91", IF ( [Sum of sales] = _max, "#3d6e35" ) )If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hello,
First, thank you so much for the hard work helping me. The only issue i'm having is that each individual Month is in fact it's own unique measure (please see screen shot). The reason for this is because i'm appending a data file with YTD revenue information and appending onto the previous one. In this case, I have subtract each previous month in order to get the revenue earned "for that particular month" - not YTD. So, since each month is it's own unique measure....in your example, i cannot use one particular field (Sum of Sales) to capture all months. Is there a way to use the MINX and MAXX features in your formula, but list out each unique month measure? Please let me know if this makes sense.
Hi gowen_WHG ，
Are the measures circled in red in the figure below calculated using different logic? Is it possible to combine these into one measure? If it is convenient, could you please provide me with the formulas and calculation logic for these measures? I'll check if there are other alternatives to achieve the same function later. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi,
There should be no need to write a measure per column. My guess is that your data is not well structured. You must use the "Unpivot Other Columns" feature in the Query Editor to get all months/dates in a single column.
