IF per ID

[Anonymous]

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!!

[v-juanli-msft · Accepted 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.

[v-juanli-msft]

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.

[Greg_Deckler]

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...

[jthomson]

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

[Anonymous]

Hey!

Cant do it, "Forecast" Is a measure, not a column 😧

Any other ideas?