Trying to calculate the variance on matrix preview visual on one field vs. an autosum of another

[Rsanjuan]

Trying to calculate the difference between one field (YTD Budget) vs. the autosum of another (Expense amount).

Using this:   Variance = Overall[YTD Budget]-Overall[Expense Amount]

However, it is calculating the difference between the individual expenses when compared to the budget.  Only want to calculate the  difference of the total column that is autosummed vs. the ytd budget.  The YTD budget is a column and it's the same for all the individualized expenses, I just didn't autosum it.

Any ideas?  Thanks!

[GilbertQ]

Hi @Rsanjuan

What I would suggest doing is to not use the Values in your tables, but rather to create measures. This ensures that you get the measure that you require, if it is a total or just a sum.

Then when you want the variance it can then just be [Sum measure] - [Total Measure]

With your example there should be a measure for "YTD Budget" (Which can use a TOTALYTD DAX function)

And then your "Expense Amount" should be: Exp Amount = SUM('Overall'[Expense Amount])

This is much easier to work with, troubleshoot as well as will ensure that it performs as quickly as possible.

[Rsanjuan]

Hi @GilbertQ,

The YTD Budget is something that needs to be calculated from the annual budget field.  For example, the annual budget is $32,400.  So to calculate the YTD Budget, I did (32,400/12)*2 months in the Query Editor.  So all the itemized expenses to one specific code would have the same YTD budget value.  When I try to put the YTD budget as a column, it does it for all the itemized expenses instead of just using the total.

I created a measure for the expense amount using the sum expression.  How to do the DAX expression where it only takes the single value of the YTD budget and subtract the total from it?

Thanks for your help!

[GilbertQ]

Hi @Rsanjuan

I am not sure why you would want to calculate the YTD in the Query Editor. The reason is what if your year is not complete and you are dividing by 12, that could potentially lead to incorrect results?

You could achieve this by creating a Measure when the data has loaded.

If you create the above measure, it will then be easy to create your Variance measure which will be [Measure A] - [Measure B]

Do you perhaps have some sample data to share?