Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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? 

7 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    5 years ago

    Hi Anonymous ,

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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>

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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