Calculate monthly variance

[Marcus_E]

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 Centre	Allocation	Costs	Month
Cost centre 1	Actual	$                    100	Jan-19
Cost centre 2	Actual	$                    350	Jan-19
Cost centre 3	Actual	$                2,500	Jan-19
Cost centre 1	Budget	$                      80	Jan-19
Cost centre 2	Budget	$                    400	Jan-19
Cost centre 3	Budget	$                2,600	Jan-19
Cost centre 1	Actual	$                      95	Feb-19
Cost centre 2	Actual	$                    420	Feb-19
Cost centre 3	Actual	$                3,000	Feb-19
Cost centre 1	Budget	$                      80	Feb-19
Cost centre 2	Budget	$                    400	Feb-19
Cost centre 3	Budget	$                2,600	Feb-19
Cost centre 1	Actual	$                    110	Mar-19
Cost centre 2	Actual	$                    380	Mar-19
Cost centre 3	Actual	$                2,400	Mar-19
Cost centre 1	Budget	$                      80	Mar-19
Cost centre 2	Budget	$                    400	Mar-19
Cost centre 3	Budget	$                2,600	Mar-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,950	Jan-19
Budget	$                3,080	Jan-19
Actual	$                3,515	Feb-19
Budget	$                3,080	Feb-19
Actual	$                2,890	Mar-19
Budget	$                3,080	Mar-19

Appreciate any thoughts or suggestions.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Nathaniel_C]

@Marcus_E 
Try this:

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

[Nathaniel_C · Accepted solution]

And with a filter it looks like this:

[Marcus_E]

@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:

---

[Nathaniel_C]

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!

[Marcus_E]

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