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?
@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
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.
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!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.