Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 @Tanushree_Kapse , I'll give it a try. Meanwhile, would I require these two tables to be joined on state and 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>
Hi @lalTel20 ,
Please find the attached file:
https://www.dropbox.com/sh/g4o6nz77gj8w6mc/AAAcsP3FMJ83PZvyqCB53ILAa?dl=0
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!
Thanks.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |