How to add a column showing the % variance between that row's value and the table's bottom avg?

[antoniosantos]

Hi!

I have been struggling to get this one done and I need you help! 🙂

The table below should have an additional column showing the difference between 3,35 (first row, for example) and 3,36 (bottom avg) in %.

I have tried the quick measures, but none of them seems to help.

Thanks in advance!

Antonio

[amitchandak]

@antoniosantos , Your measure is [QTD Media/Und]

Try a measure like

diff= calculate( [QTD Media/Und] , allselected())  - [QTD Media/Und] 

or

diff= calculate( [QTD Media/Und] , allselected(DataTable))  - [QTD Media/Und] 

[antoniosantos]

Thanks a lot for the suggestions. However, if I try

diff= calculate( [QTD Media/Und] , allselected())  - [QTD Media/Und] 

It expands the table, showing previous dates that are not within the selected ones via slicer.

if I do:

diff= calculate( [QTD Media/Und] , allselected(DataTable))  - [QTD Media/Und] 

It shows the result on a row by row basis, showing 0 because they are similar, while it should compare with the value at the bottom.

Thank you!

[v-rzhou-msft · Accepted solution]

Hi @antoniosantos ,

I think you can try to add an IF() function in your code.

diff = 
IF([Qtd/Und]<>BLANK(),CALCULATE([Qtd/Und],ALLSELECTED()) - [Qtd/Und])

My Sample:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[antoniosantos]

Thanks for taking the time to understand, despite how trivial this may seem to you!

Cheers,

Antonio