Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have two tables:
Forecast
Current amount
These tables have the same structure. Clients and amounts, example:
Client | Amount |
A | 10 |
A | 2 |
A | 3 |
B | 1 |
B | 2 |
C | 10 |
C | 10 |
C | 10 |
C | 10 |
I created a pivot in which I can visualize the current amount vs the forecast. Now, I created a calculated column with an "if" function that generates a description depending the % of "Accuracy" between the current amount vs the forecast, example:
Client | Amount | Forecast | Accuracy | Description |
A | 15 | 15 | 100% | Safe |
B | 3 | 1 | 33% | Under the forecast |
C | 40 | 20 | 50% | Under the forecast |
Here's the problem: The column desciption evaluates the accuracy row per row, it doesnt calculate it overall per account so every account appears as "Under the forecast" because of course the amount per row its not going to get even close to the forecast.
I managed to create a measure that gets the job done but Im unable to use measures as legends in a chart (which is the whole point of this honestly) So i need it to be in a column, Any ideas?
Here's the measure for the description that get the job done:
Description = IF(and([Accuracy]<0.8,[Accuracy]>0), "Under the forecast", IF(and([Accuracy]>1, [Accuracy]<=1.5), "Over the forecast", IF(and([Accuracy]>=0.8, [Accuracy]<=1), "Safe", IF(or([Accuracy]>1.5, [Accuracy]<0.1), "wtf", IF(ISBLANK([Accuracy]), BLANK())))))
If you have any questions, please let me know!
Thank you!!
Solved! Go to Solution.
Hi @Anonymous
Assume you have tables as below
Create columns
Accuracy = RELATED('Table 2'[Forecast])/CALCULATE(SUM([Amount]),ALLEXCEPT('Table','Table'[Client])) Description = IF(and([Accuracy]<0.8,[Accuracy]>0), "Under the forecast", IF(and([Accuracy]>1, [Accuracy]<=1.5), "Over the forecast", IF(and([Accuracy]>=0.8, [Accuracy]<=1), "Safe", IF(or([Accuracy]>1.5, [Accuracy]<0.1), "wtf"))))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Assume you have tables as below
Create columns
Accuracy = RELATED('Table 2'[Forecast])/CALCULATE(SUM([Amount]),ALLEXCEPT('Table','Table'[Client])) Description = IF(and([Accuracy]<0.8,[Accuracy]>0), "Under the forecast", IF(and([Accuracy]>1, [Accuracy]<=1.5), "Over the forecast", IF(and([Accuracy]>=0.8, [Accuracy]<=1), "Safe", IF(or([Accuracy]>1.5, [Accuracy]<0.1), "wtf"))))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Greg_Deckler 's suggestion may helpful.
If not, please show more details:
How do you caluclate the Forcast measure?
Do you pivot table in Edit queries,
or just use table/matrix visual to pivot it instead of change data structure?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
Try:
Measurename =
var accuracy = divide (sum[forecast],sum[amount])
return if(accuracy > 1.5,"wtf",if(accurary>1, "over",if(accuracy>=0.8,"safe",if(accuracy>0,"under","wtf"))))
Hey!
Cant do it, "Forecast" Is a measure, not a column 😧
Any other ideas?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |