Conditional formatting for two different months with two different RAG targets

[Anonymous]

Hi,

Am trying to write a consolidated DAX for conditional formatting based on two different months and two different RAG targets.

I have prepared a calculated measure columns to derive the actuals, aganist targets. Am trying to now fix the rag color formatting based on the actuals achiveved, here targets and rag colors are different for two different months, how can i write a dax measure in this scenario

for an instance for the month of Jan my target is 10% and for feb my target is 15%..MY Actual for Jan is 11% and for Feb is 14% now i would like the RAG color to return as green for Jan and red for Feb ..these conditions would be based on the calculated measure column that i created earlier, kindly assist me as all this fall under same column so at the end when i apply conditional formatting it should pick up the condition

[parry2k]

@Anonymous @v-jingzhang already provided you the solution. Did you looked at the @v-jingzhang pbix shared with you? Is that not working for you? If not, provide more details about why it is not working.

You should look at the solution which is already provided and explain if that didn't work for you and you need further assistance. It is unknown at this point if you tested an existing solution or not? 

[PhilipTreacy]

Hi @Anonymous 

Sorry I didn't know you had replied.  You need to @ me in replies - type @ and then select the person's name/handle.

Regards

Phil

[Anonymous]

Hi @PhilipTreacy 

Sorry for that, as you can see i have shared the data for reference kindly assist me. 

[Anonymous]

Hi Philip, 

Thank you for replying, above table is the sample data.

Here,as you see we have months and its targets( which are different), i tried a uniform formatting through rules, however since we have 2 different targets i for each month i couldnt figure out how to write a dax for this, kindly assist

Thanks in Advance!

[v-jingzhang]

Hi @Anonymous 

Create a measure like:

M_Color =
VAR Diff =
    SELECTEDVALUE ( 'Table'[Actuals] ) - SELECTEDVALUE ( 'Table'[Target] )
VAR Color =
    SWITCH (
        TRUE (),
        Diff >= 0, "Green",
        -0.01 < Diff
            && Diff < 0, "Yellow",
        Diff <= -0.01, "Red"
    )
RETURN
    Color

 Then go to Format > Conditional formatting > Select Actuals and Background color > Advanced controls > Format by Field value based on Field M_Color. Here is the PBIX file you can download.

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Anonymous]

Hi @v-jingzhang 

Thank you for the update 🙂

could you let me know how can i change the color target for each month as you see in above ref table there are different targets for each alternate month

[PhilipTreacy]

Hi @Anonymous 

Can you please post some sample data so we can see what the structure of your data is : column names etc.  

thx

Phil

[parry2k]

@Anonymous you should calculate a measure for the color and add month condition in it along with % and then in conditional formatting, use field value option and use this measure to control the color.

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.⚡

[Anonymous]

thanks for your inputs, could you kindly help me with the dax formula instead