topic Re: Multiple Conditional Formatting with diff Data Formats in DAX Commands and Tips

Multiple Conditional Formatting with diff Data Formats

NilR — Fri, 11 Mar 2022 18:14:22 GMT

I need help on Conditional Formatting!
I have a measure with multiple Data formats Such as ( Number, Currency, and Percent) . I would like to setup conditional formating to show any value negative in Red and Any value >= 0 in Green.

However, at the bottom you can see that My First Number that is not Currency "0,00" and the Last one is Percent "0.00%" they stayed black . Is there any way to change those numbers into Red if they are Negative or Green if >=1 ?

Financial Variance = IFERROR(SWITCH(TRUE(), SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="Member Months", FORMAT([Member Months]-[Member Months_ALL_BVS],"0,00"), SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="PMPM", [PMPM]-[PMPM_ALL_BVS], SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="PEPM", [PEPM]-[PEPM_ALL_BVS], SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="Revenue", [Premium (Revenue)] -[Premium_ALL_BVS], SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="Pricing Policy", FIXED(0,"Standard"), SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="Medical Claims", [MEDICAL CLAIMS] - [MEDICAL_CLAIMS_ALL_BVS], SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="Pharmacy Claims", [RX CLAIMS] - [RX_CLAIMS_ALL_BVS], SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="MLR", FORMAT([MLR] - [MLR_ALL_BVS] ,"0.00%"), SELECTEDVALUE('III - Financial Metrics'[Financial Metrics])="Capitation", [TOTAL_CAP] - [TOTAL_CAP_ALL_BVS],0),0)

My Conditional Formatting:

My result:

Desired Result:

Thanks!

Re: Multiple Conditional Formatting with diff Data Formats

amitchandak — Fri, 11 Mar 2022 06:05:06 GMT

NilR , The last Percent , should be number

Also you can try color measure

How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

Re: Multiple Conditional Formatting with diff Data Formats

NilR — Fri, 11 Mar 2022 14:22:05 GMT

I am not coloring my metrics, I want to color them if it is negative then turn RED or if it is positive Then Green.

Re: Multiple Conditional Formatting with diff Data Formats

v-yalanwu-msft — Tue, 15 Mar 2022 06:27:17 GMT

Hi, NilR ;

I found the type of Financial Variance is text because here use FORMAT().

so if coniditonal format is rules, may be cause error. I think you could create other measure as Color based on a calculation.

Such as:

Measure = IF(CONTAINSSTRING([Financial Variance],"%"),"Green",IF([Financial Variance]=0,"Black","Red"))

The final output is shown below:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Multiple Conditional Formatting with diff Data Formats

NilR — Mon, 21 Mar 2022 13:45:20 GMT

Thank you very much! 🙂