Conditional Formatting by Row Across Multiple Measures

[gowen_WHG]

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!

[Ashish_Mathur]

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.

[Anonymous]

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

[gowen_WHG]

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.

[Anonymous]

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

[gowen_WHG]

Hi @Anonymous,

Yes, they are calculated using the same logic.  I have a sample simplified pbix with these data.  I clicked the link and says that I need to be a super user to upload...how do i do that, and how can I get you this pbix file?

I cannot thank you enough for your time helping me with this.

Sincerely,

@gowen_WHG

[gowen_WHG]

Hello @Anonymous ,

Thank you so much for all of your help, but my IT department head notified me today to refrain from sharing files...even sample ones.  I will continue to refer to your original measure coding and spend more time with it to try and make it work.  Thanks again! 

[Anonymous]

Hi @gowen_WHG ,

You can refer the following link to upload your pbix fileto other locations (grant the access to me) and share the link with me. Thank you.

How to upload PBI in Community

Best Regards