Forum Discussion
Calculate monthly variance
And with a filter it looks like this:
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 _actuaLAnd with a filter it looks like this:
Many thanks for the prompt solution(s). They work perfectly for what I'm trying to achieve.
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:
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!
