How to create a measure or a DAX formula to get only figures from the column that are above 5%

[AstaBr]

Hi, we need to get projects that have a variance to plan over 5% or £100k colored in red. Enything up till that figure (including £0) should not have color. The colomn we need to work with is the '2024/2025 Variance from plan' from the Project list data set.

 

[Anonymous · Accepted solution]

Hi @AstaBr ,

Is your requirement a conditional formatting of the planned values based on the percentage obtained by subtracting the planned values from the predicted values, if I understand correctly, here are my steps:

1.Create simple data:

2.Create a measure and use it for conditional formatting, you can make some adjustments according to your needs.

Measure = 
VAR _divide=DIVIDE(MAX('Project list'[2024/2025 Variance from plan]),MAX('Project list'[2024/2025 Planned detailed financials]))
RETURN 
IF(_divide<-0.05,1,IF(_divide>-0.05&&_divide<=0,0))

3.Background color settings:

4.The final result is as follows:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous · Accepted solution]

Hi @AstaBr ,

Is your requirement a conditional formatting of the planned values based on the percentage obtained by subtracting the planned values from the predicted values, if I understand correctly, here are my steps:

1.Create simple data:

2.Create a measure and use it for conditional formatting, you can make some adjustments according to your needs.

Measure = 
VAR _divide=DIVIDE(MAX('Project list'[2024/2025 Variance from plan]),MAX('Project list'[2024/2025 Planned detailed financials]))
RETURN 
IF(_divide<-0.05,1,IF(_divide>-0.05&&_divide<=0,0))

3.Background color settings:

4.The final result is as follows:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AstaBr]

Hi @Anonymous 

thank you a lot for the above formula, however, i just noticed that the positive figures are also coloured which shouldn't be. Please could you recheck the created measure for me.

[Anonymous · Accepted solution]

Hi @AstaBr ,

I tried to add a row where the data is positive, but the result is correct:

Can you try the following code to equalize the remaining cases to 2:

Measure = 
VAR _divide = DIVIDE(MAX('Project list'[2024/2025 Variance from plan]), MAX('Project list'[2024/2025 Planned detailed financials]))
RETURN 
IF(_divide <= -0.05, 1, IF(_divide > -0.05 && _divide <= 0, 0, 2))

There's also a possibility that you're applying multiple conditional formats to the column, causing a prioritization issue.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AstaBr]

Amaizing thank you so much.