cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.