Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

Background Color Conditional Format Based on External Rules


The Table on the left is actual aggregated value of sales across different states/Countries. While the table on left is a static table which has forecasted sales for each country/state. 

I would like to get the measure Sum Sales background colored based on the forecast raw input table. Currently I'm able to achieve this using a if else statement but it becomes too complex for huge list of countries. 

What could be a easy fix for this? 

Helper I
Helper I

@Tanushree_Kapse  Thanks for your comments. I got the gist of the solution. A calculated column and a measure as you suggested should together help solve this. 

So this is what i did. 

Step 1 : Created a calculated column which is made by 

Forecasted Value = LOOKUPVALUE(Reference[Forecast Sales 1],Reference[Country],'Fact'[Country],Reference[State],'Fact'[State])
Step 2: Created a measure called variance as 
Variance = SUM('Fact'[Sum Sales]) - AVERAGE('Fact'[Forecasted Value])



Helper I
Helper I

Thanks @Tanushree_Kapse , I'll give it a try. Meanwhile, would I require these two tables to be joined on state and country? 

@lalTel20 , Yes join them on country.

Thanks. But, wouldn't that create a calculation Error, since the forecast is not same for the entire country, it varies with the state. And i need to find the variance for each state. <Sorry if i havent understood this correctly>

Thank you for the file. 

In the provided file the join is based on State, which in this example are not unique. And as soon as I have additional record in the agg table which has the same state, it errors out. 







Impactful Individual
Impactful Individual

Hi @lalTel20 ,

Create one measure to compare the two values:
SumVsForecast= SUM(Forecast Sales)- SUM(SumSales)

Then apply conditional formatting to the Sum Sales column:
Conditional formatting-> Background Color -> Format by : Rules -> Based on Field- SumVsForecast

If value is greater than equal to MIN number and less than equal to 0 number then choose the color
Add new rule:
If value is greater than equal to 0 number and less than equal to MAX number then choose the color

I hope this helps!
Mark this as a solution if it anwsers your question. Kudos are always appreciated!


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors