Conditional formatting based on values

[cmllrcg]

Hello!

I am new to Power BI and currently working on building a matrix for my report and I was wondering if there is a way to put in background colors based on the values of items from the same table. The output should be that if the value of for example Strokes per Minute - Actual > Strokes per Minute - Plan then it will be be green on the matrix and if it is < then it will be red.

In the matrix, I would be displaying Strokes per Minute - Actual.

Thank you and I appreciate your help in advance!

[v-kkf-msft · Accepted solution]

Hi @cmllrcg ,

Try to create measure:

Color = 
var SPM_A = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes per Minute - Actual"
    )
var SPM_P = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes per Minute - Plan"
    )
return 
    SWITCH(
        MAX('Events and Accumulators'[Event]),
        "Strokes per Minute - Actual", IF(SUM('Events and Accumulators'[Value]) > SPM_P, "green", "red"),
        "Strokes per Minute - Plan", IF(SUM('Events and Accumulators'[Value]) > SPM_A, "green", "red")
    )

Then format background color based on measure Color:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft · Accepted solution]

Hi @cmllrcg ,

Try to create measure:

Color = 
var SPM_A = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes per Minute - Actual"
    )
var SPM_P = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes per Minute - Plan"
    )
return 
    SWITCH(
        MAX('Events and Accumulators'[Event]),
        "Strokes per Minute - Actual", IF(SUM('Events and Accumulators'[Value]) > SPM_P, "green", "red"),
        "Strokes per Minute - Plan", IF(SUM('Events and Accumulators'[Value]) > SPM_A, "green", "red")
    )

Then format background color based on measure Color:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cmllrcg]

Hello, @v-kkf-msft!

Really appreciate your time looking into this, I think this solved it. Quick question though, do I just add other variables and values in the Switch to be able to apply these in the other ones too like for ADC Actual and Plan, and Downtime Actual and Plan? 

Thank you very much! Hope you have a good day.

[v-kkf-msft · Accepted solution]

Hi @cmllrcg ，

If you want to add other variables, I find two ways.

1. Try the following formula:

Color = 
var ADC_A = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "ADC - Actual"
    )
var ADC_P = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "ADC - Plan"
    )
var DT_A = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Downtime - Actual"
    )
var DT_P = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Downtime - Plan"
    )
var GSPH_A = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "GSPH - Actual"
    )
var GSPH_P = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "GSPH - Plan"
    )
var S_A = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes - Actual"
    )
var S_P = CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes - Plan"
    )
var SPM_A = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes per Minute - Actual"
    )
var SPM_P = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        'Events and Accumulators'[Event] = "Strokes per Minute - Plan"
    )
return 
    SWITCH(
        MAX('Events and Accumulators'[Event]),
        "Strokes per Minute - Actual", IF(SUM('Events and Accumulators'[Value]) > SPM_P, "green", "red"),
        "Strokes per Minute - Plan", IF(SUM('Events and Accumulators'[Value]) > SPM_A, "green", "red"),
        "ADC - Actual", IF(SUM('Events and Accumulators'[Value]) > SPM_P, "green", "red"),
        "ADC - Plan", IF(SUM('Events and Accumulators'[Value]) > SPM_A, "green", "red"),
        "Downtime - Actual", IF(SUM('Events and Accumulators'[Value]) > DT_P, "green", "red"),
        "Downtime - Plan", IF(SUM('Events and Accumulators'[Value]) > DT_A, "green", "red"),
        "GSPH - Actual", IF(SUM('Events and Accumulators'[Value]) > GSPH_P, "green", "red"),
        "GSPH - Plan", IF(SUM('Events and Accumulators'[Value]) > GSPH_A, "green", "red"),
        "Strokes - Actual", IF(SUM('Events and Accumulators'[Value]) > S_P, "green", "red"),
        "Strokes - Plan", IF(SUM('Events and Accumulators'[Value]) > S_A, "green", "red")
    )

2. If you have many variables, you can create a comparison table and relationship like this:

Then create the following measure to replace measure Color.

Measure = 
var comparison_event = 
    CALCULATE(
        SUM('Events and Accumulators'[Value]),
        FILTER(
            ALL('Events and Accumulators'),
            'Events and Accumulators'[Event] = MAX(Comparison[Comparison Event])
            && 'Events and Accumulators'[StartTime] = MAX('Events and Accumulators'[StartTime])
        )
    )
return 
    IF(
        SUM('Events and Accumulators'[Value]) > comparison_event, 
        "green",
        IF(
            SUM('Events and Accumulators'[Value]) < comparison_event,
            "red"
        )
    )

Best Regards,
Winniz

[cmllrcg]

Thank you so much!

[parry2k]

@cmllrcg what I'm doing with this pbix file I have no idea. You haven't had any table/matrix visual and no details what need to be done.

[parry2k]

@cmllrcg it will be easier if you share pbix file with sample data. The doc is very well explained, if you have an issue following it better I do it in your pbix file. Remove any sensitive information before sharing.

[cmllrcg]

Hello,

https://drive.google.com/file/d/14vSl6OjSk8n_Vi6R8ZvMxhrONONsQKtT/view?usp=sharing - here is the link to the sample dataset. thanks!

[parry2k]

@cmllrcg yes you can 100% do this. Did you checked this blog post Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[cmllrcg]

Yes, I have but I am still quite confused as to how to do it because I think it will require some filtering to do so? Thanks though!