Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NadeemAhamed
Helper IV
Helper IV

Calucating Variance, Achievement from Plan and Actual

Dear All, 

Good Day.

 

I am preparing Power BI Report.

 

I have data like below.

NadeemAhamed_0-1706095005533.png

 

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.

2 REPLIES 2
v-weiyan1-msft
Community Support
Community Support

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.

vweiyan1msft_0-1706174757588.png

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.

vweiyan1msft_1-1706174810278.png

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.

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.

NadeemAhamed_0-1706175950509.png

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.