Calucating Variance, Achievement from Plan and Actual

[NadeemAhamed]

Dear All, 

Good Day.

I am preparing Power BI Report.

I have data like below.

This values are not in source table. we have to create measures or column for this below data. 

1. Difference (Second/First) -> should be in rows

2. Variance (Actual-Plan)

3. Acheivement (Actual/Plan)

After creating measures and columns for the above calculations, we have to add filtrer for division. 

When we are selecting the single division Variance and Acheivement column is working fine. 

When are selecting multiple division. Variance and Acheivement values are getting sum as Plan and Actual values are getting, but as per the calculation Variance and Achievement should not be sum values. Acutally Variance and Achievement values should depend on the Sum of Plan and Sum of Actual (Depend on Division selection).

Thank you in advance...👍

When i am transfering into Power BI data formate is getting change.

[v-weiyan1-msft]

Hi @NadeemAhamed ,

I am not sure if I understood your question correctly.
Based on my understanding, here is the data I created from the sample you provided.

Please try code as below to create two Calculated Column.

Variance (Actual-Plan) = 'Table'[Actual] - 'Table'[Plan]

Acheivement (Actual/Plan) = DIVIDE('Table'[Actual],'Table'[Plan])

When you select "Admin" and "IT" in the slicer, Result is as below.

If I've misunderstood you, please provide detailed sample data and the results you are hoping for. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NadeemAhamed]

Dear @v-weiyan1-msft 

Thank you for valuable time to reply.

Your Understood is right, adding to that point. 

Yes we can calucate the variance and acheivent column values by using the DAX  as you have provided above. 

but how to caluclate Difference.

After targting above calculation we have to show them for all division in this below format.

Here we are selected multiple division, the issue is variance and achievement values also getting sum(variance) and sum(Achievement) but we should do sum for variance and achievement.

Actually first we have to do sum for First and Secound value.

Then we have to calculate variance and achievement for the sum(First) and sum(Secound).