substract with decimal

[IF]

Hi,

I want to substract between two measures and colour the result. For example;

Measure1	Measure2	Rounded Measure1	Rounded Measure2	Difference
0.196	0.204	0.20	0.20	0
0.191	0.204	0.19	0.20	-0.01
0.197	0.195	0.20	0.20	0

I used table, but actually it is not possible to use table as they are measure. I use format change:

However, I can not change the colour of the result with the calculation logic to use the result Measure3=Rounded Measure1-Rounded Measure2:

If the difference is 0, it should be green, but it doesn't calculate based on the rounded measure. In the third case, it should be green, but it is shows the value with red.

How can I use colouring based on the rounded measures?

Thanks in advance!

[lbendlin · Accepted solution]

You can use a simpler approach. Your Measure3 can directly compute the desired RGB color, and then your conditional formatting can use the value of Measure3 directly as well (rather than rules)

[Fowmy · Accepted solution]

@IF 

Use Measure 3  as the measure for the conditional formatting as follows:

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[v-robertq-msft · Accepted solution]

Hi, @IF 

According to your description and picture, I guess that you want to get the difference of the round value of the two measures and set conditional format based on their difference, you can try my steps:

1. I suggest you to use DAX to get the round value of the two measures, you can create these measures:

Rounded Measure1 = ROUND([Measure1],2)

Rounded Measure2 = ROUND([Measure2],2)

Measure3 = [Rounded Measure1]-[Rounded Measure2]

Color =

SWITCH(

    TRUE(),

    [Measure3]=0,"Green",

    [Measure3]<0,"Red",

    [Measure3]>0,"Black")

2. Then create a table chart and place columns and open the conditional format, like this:

3. Then set the conditional format like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, @IF 

According to your picture, I guess that you want to set the conditional format of the font color of the value in a Card chart, I don’t think it’s possible. Because conditional format can only apply to table and matrix charts in Power BI, you can not set conditional format for other charts.

Therefore, I suggest you to still use a table chart to achieve this, you make some changes based on my solution above:

Go to the Format forum, select “Conditional formatting”, close the “Background color” and open the “font color”, like this:

Then set like this:

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[IF · Accepted solution]

Yes, I thought of using a card to change the color accordingly. Thanks a lot! Happy holidays!

[IF]

Thanks a lot All of you!

 if I want to display the value in red, green or black color by calculating the difference between two measures, what would be the best?

All the best

[v-robertq-msft · Accepted solution]

Hi, @IF 

According to your description and picture, I guess that you want to get the difference of the round value of the two measures and set conditional format based on their difference, you can try my steps:

1. I suggest you to use DAX to get the round value of the two measures, you can create these measures:

Rounded Measure1 = ROUND([Measure1],2)

Rounded Measure2 = ROUND([Measure2],2)

Measure3 = [Rounded Measure1]-[Rounded Measure2]

Color =

SWITCH(

    TRUE(),

    [Measure3]=0,"Green",

    [Measure3]<0,"Red",

    [Measure3]>0,"Black")

2. Then create a table chart and place columns and open the conditional format, like this:

3. Then set the conditional format like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[AllisonKennedy]

@IF  You have the 'equal to' in the wrong line. If you want 0 to be green, it must say 'is less than or equal to' 0 then green. and 'is greater than' 0 then red.

[lbendlin]

Here's a sample code.  you can also use the hash codes, like "#FF0000" for red etc.

Measure3:=switch(true(),Measure1>Measure2,"green",Measure1<Measure2,"red","grey")

[Fowmy · Accepted solution]

@IF 

Use Measure 3  as the measure for the conditional formatting as follows:

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Fowmy]

@IF 

Please check the attached file:  https://1drv.ms/u/s!AmoScH5srsIYgY0ZdMZNP_JG8I_YHg?e=xAikif

Refer to this article for more information.:https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[lbendlin · Accepted solution]

You can use a simpler approach. Your Measure3 can directly compute the desired RGB color, and then your conditional formatting can use the value of Measure3 directly as well (rather than rules)

[IF]

I am not very good at Power BI. How could I do it?