Forum Discussion

Marcus_E's avatar
Marcus_E
Icon for Helper I rankHelper I
6 years ago
Solved

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.

 

Need Help

6 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago

      And with a filter it looks like this:

       

      • Marcus_E's avatar
        Marcus_E
        Icon for Helper I rankHelper I
        6 years ago

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