cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Marcus_E Frequent Visitor
Frequent Visitor

Calculate monthly variance

Hi everyone

Fairly new here to BI and am grappling with a suitable way to calculate the difference between Actual costs versus Budget costs on a monthly basis.

Ideally I would like to have a DAX measure that can pull together the total Actual costs and then seperately Budget costs incurred during successive months.

A simplified version of my data set is below:

Cost CentreAllocationCostsMonth
Cost centre 1Actual $                    100Jan-19
Cost centre 2Actual $                    350Jan-19
Cost centre 3Actual $                2,500Jan-19
Cost centre 1Budget $                      80Jan-19
Cost centre 2Budget $                    400Jan-19
Cost centre 3Budget $                2,600Jan-19
Cost centre 1Actual $                      95Feb-19
Cost centre 2Actual $                    420Feb-19
Cost centre 3Actual $                3,000Feb-19
Cost centre 1Budget $                      80Feb-19
Cost centre 2Budget $                    400Feb-19
Cost centre 3Budget $                2,600Feb-19
Cost centre 1Actual $                    110Mar-19
Cost centre 2Actual $                    380Mar-19
Cost centre 3Actual $                2,400Mar-19
Cost centre 1Budget $                      80Mar-19
Cost centre 2Budget $                    400Mar-19
Cost centre 3Budget $                2,600Mar-19

 

I'm struggling to pull out the Actual costs and separate from the Budget costs but I'm trying to achieve a result that would look something like this:

Actual $                2,950Jan-19
Budget $                3,080Jan-19
Actual $                3,515Feb-19
Budget $                3,080Feb-19
Actual $                2,890Mar-19
Budget $                3,080Mar-19

 

Appreciate any thoughts or suggestions.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Calculate monthly variance

And with a filter it looks like this:

bud1.PNG

 

View solution in original post

6 REPLIES 6
Nathaniel_C Super Contributor
Super Contributor

Re: Calculate monthly variance

@Marcus_E 
Try this:

bud.PNG

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Actual costs = 
var _actual = CALCULATE(SUM('Table'[Costs]),ALLEXCEPT('Table','Table'[Cost Centre],'Table'[Month]),'Table'[Allocation]= "Actual")

return _actual

Budget Costs = 
var _actual = CALCULATE(SUM('Table'[Costs]),ALLEXCEPT('Table','Table'[Cost Centre],'Table'[Month]),'Table'[Allocation]= "Budget")

return _actuaL
Super User
Super User

Re: Calculate monthly variance

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nathaniel_C Super Contributor
Super Contributor

Re: Calculate monthly variance

And with a filter it looks like this:

bud1.PNG

 

View solution in original post

Marcus_E Frequent Visitor
Frequent Visitor

Re: Calculate monthly variance

Many thanks for the prompt solution(s). They work perfectly for what I'm trying to achieve.

Marcus_E Frequent Visitor
Frequent Visitor

Re: Calculate monthly variance

@Nathaniel_C 

 

Just wondering if you would have any thoughts on how to introduce a measure to calculate the Variance between Budget and Actual? I realise that it would normally be a case of subtracting Total Actual from Total Budget which works fine if I'm only after a static total variance value for the nominated period. The problem I've found is that when introducing other slicer filters on the visual, the Total Actual, Total Budget and Total Variance values do not change.

 

For example I added another data field of Cost Responsibility and when attempting to use the slicer filter the three actual, budget and variance values won't change. Therefore I've had to resort to using Filters on This Page to link the values back to the slicer otherwise they remain unchanged.

 

I'm most likely missing something simple here, but the variance is the key to the visuals I'm trying to produce and I really don't want to add a calculated column into the data set if it can be avoided.

 

Many thanks.


@Nathaniel_C wrote:

And with a filter it looks like this:

bud1.PNG

 


 

Nathaniel_C Super Contributor
Super Contributor

Re: Calculate monthly variance

Hi @Marcus_E ,

Is it possible to share a sanitized pbix with us? Or dummy up one.  The important parts would be the slicers, tables and the relationships that impact this.  I am sure the solution is there somewhere, there are a lot of brilliant people on this forum.  I might even post this as a new issue.  If you send me a pm, I will make sure to look at it, although I have a busy weekend. Another thing that I have seen some posters do is to put that top 5 on the the leaderboard.  @Nathaniel_C  will get a message sent to my box.

Good luck!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 169 members 1,645 guests
Please welcome our newest community members: