Forum Discussion
IF per ID
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!!
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
MaggieCommunity 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.
5 Replies
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"))))
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/ba-p/279563
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
MaggieCommunity 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
MaggieCommunity 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.
