Excel Forum

number coloring positive green negative red in card visual in power bi

Yuan Zhang

Member

Members

Forum Posts: 32

Member Since:
December 2, 2021

Offline

May 4, 2023 - 6:22 am

hi all,

How can I make the number coloring based on the value (positive green negative red) in card visual in power bi? I found this post but my codes don't seem to work? I never did HTML codes. so something wrong there? Or do you have other solutions? Thank you!

In my code (

VAR var_Volume_Current_Year_vs_Last_Year = CALCULATE(SUM(T_Production_Volume[Volume in Ton]),FILTER(T_Production_Volume,T_Production_Volume[Current FY?]= "Current Year"))-CALCULATE(SUM(T_Production_Volume[Volume in Ton]),FILTER(T_Production_Volume,T_Production_Volume[Current FY?]= "Budget"))

) is an existing measure calculation is correct, so no error with that part I think.

Attachments

See Answer

Philip Treacy

Admin

Forum Posts: 1529

Member Since:
October 5, 2010

Online

May 4, 2023 - 9:53 am

Hi,

I wrote about this topic here Conditional Formatting in Power BI

You can do this 2 ways:

1) Using formatting based on rules

2) Using formatting based on a field value

Using rules is easier to set up but can be less reliable as you have to explicitly say what the smallest and largest value in your data are, and this might not be known, or change.

To set up formatting based on a rule

1. Add the card

2. Add the value to the card

3. Go to Format Visual -> Callout value -> click on the fx button

4. Add a rule like this then click OK

This is using the values calculated in the DAX measure you have already written, in my case I've named that measure Difference Using Rules CF and the code for that is

Difference Using Rules CF = SUM('Actuals & Budget'[Actuals]) - SUM('Actuals & Budget'[Budget])

Formatting based on Field Value

This method requires you to write a separate DAX measure to choose what color to apply to the visual

That DAX looks like this

Difference Using Field CF =

VAR _diff = SUM('Actuals & Budget'[Actuals]) - SUM('Actuals & Budget'[Budget])

RETURN

IF (_diff >= 0, "green", "red")

To use this to set the color of the card text:

1. Add the card

2. Add the value to the card (this is the same DAX measure used in method 1 to calculate difference between Budget and Actual)

3. Go to Format Visual -> Callout value -> click on the fx button

4. Add this then click OK

Either method will give you the colors you want in your card.

I prefer the 2nd approach as having a separate DAX measure to set colors is more flexible if you need to change or add colors or alter the values to which those colors should be applied.

Download my sample PBIX file for examples of both methods described here.

Regards

Phil