Calculate variance on column totals

[Anonymous]

I have a table that displays project financials including a variance based on budget vs actuals. See screen shot below:

I want to calculate the variance on the totals shown, displayed in the Variance column. The formula is simple enough, but I have not been able to find any forum answers that address this particular circumstance. The various preset options do not result in the correct outcome.

Ideally, it would also show the conditional formatting as for the rows above.

Can anyone provide some guidance?

Thanks

Steve 

[MFelix]

Hi  @Anonymous ,

What is the formula you are using for the variance?

Should work even on the total lines if it's a measure.

Regards,

MFelix

[Anonymous]

This is the formula used to calculate the variance for each row. It is a calculated column.

"= calculate(divide(sum('Actual Expense'[Expense Amount])+sum('Forecast Expense'[Forecast Amount])-sum('Initiative Funding'[Amount]),sum('Initiative Funding'[Amount])))"

This formula does not work as a measure. I tried an alternative formula as a measure and while it works for the rows it does not give the correct answer for the total.

"= IF(and(MAX('Forecast Expense'[Variance])>-1,max('Forecast Expense'[Variance])<0), MAX('Forecast Expense'[Variance])*-1, MAX('Forecast Expense'[Variance]))"

Steve

[v-lili6-msft]

hi @Anonymous 

I tried an alternative formula as a measure and while it works for the rows it does not give the correct answer for the total.

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and you could just add a new measure as below:

Measure 3 = SUMX(VALUES('Initiative Register'[Initiative Name]),[Variance1])

Result:

Regards,

Lin

[amitchandak]

The best way this can work is a measure. What is the issue you are getting when you are createing it as measure

 calculate(divide(sum('Actual Expense'[Expense Amount])+sum('Forecast Expense'[Forecast Amount])-sum('Initiative Funding'[Amount]),sum('Initiative Funding'[Amount])))

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

The problem with the formula used in a measure rather than the calculated column is that each row is the sum of individual rows in the data set and the formula does not appear to sum these individual rows, even though there is a sum function in the formula. So it shows each individual row. 

I am unable to provide a sample file without showing sensitive information. I will have to think of a way to do this if my issue cannot be resolved otherwise.

Thanks

Steve

[v-lili6-msft]

HI @Anonymous 

You may use SUMX instead of SUM in the formula, and if possible, please use some simple sample data to create a simple sample pbix file and share it with your expected output. That will be a great help for this case.

Regards,

Lin

[MFelix]

Hi @Anonymous ,

You need to add a measure with the following code:

Measure =
CALCULATE (
    DIVIDE (
        SUMX (
            'Table';
            'Actual Expense'[Expense Amount] + 'Forecast Expense'[Forecast Amount] - 'Initiative Funding'[Amount]
        );
        SUMX ( 'Table'; 'Initiative Funding'[Amount] )
    )
)

Be aware that the Table in SUMX needs to be replaced by the name of the table that you use for the row calculation on your table visual, assuming that you are using table with Initiative ID for making the visual table that should be the one place on the SUMX.

[Anonymous]

I have added the sumx function to the calculation but this gives me the error:

"A single value for column 'Forecast Amount' in table 'Forecast Expense' cannot be determined. ...."

I have placed a simplified file in Dropbox: https://www.dropbox.com/s/7genqjnf3ht08x6/Variance.pbix?dl=0. 

Hope this helps.

Steve

[MFelix]

Ypu are adding a measure or a column?

[Anonymous]

The original formula using sum is a calculated column. The amended formula using sumx is a measure. This later formula results in the error.

[MFelix · Accepted solution]

hI @Anonymous ,

The measure calculation must be:

Variance Measure = CALCULATE (
    DIVIDE (
        SUMX (
            'Initiative Register';
            SUM('Actual Expense'[Expense Amount]) + SUM('Forecast Expense'[Forecast Amount]) - SUM('Initiative Funding'[Amount])
        );
        SUMX ( 'Initiative Register';SUM('Initiative Funding'[Amount]))
    )
)

This makes the calculation for the totals and for each line.

As refer using the SUMX the table that you choose will impact in the result you have because of the context, also the calculation you use on SUMX is important in your case since there are more than one line per project you need to make the SUM within SUMX and not only the values for each column. Can you please confirm this is the expected result?

[Anonymous]

Thank you for the solution. I thought it would have to be a sum within sumx but could not make it work.

Cheers 

Steve