Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lalTel20
Helper I
Helper I

Background Color Conditional Format Based on External Rules

lalTel20_0-1626933576153.png

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? 

7 REPLIES 7
lalTel20
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])
 

lalTel20_0-1626941734092.png

 

lalTel20
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. 

lalTel20_0-1626938712836.png

 

lalTel20_1-1626938725433.png

lalTel20_2-1626938837562.png

 

 

Tanushree_Kapse
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!

Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors