Forum Discussion

Brysonds's avatar
Brysonds
Helper III
7 years ago
Solved

Need help with calculating row variance based on date selection in slicer

I'm drawing a blank on how to achieve what I feel like should be simple. Any help is appreciated!!! Data Set looks like this:   Customer Sales Date (English) Alpha 100 1/1/2019 Alph...
Need Help
v-diye-msft's avatar
v-diye-msft
Community Support
7 years ago

Hi  Brysonds ,

 

I created the sample as your requested. then add the measure:

Variance = 
var a = CALCULATE(MAX(Table1[Sales]),FILTER(ALL(Table1),[Date (English)]=MAX(Table1[Date (English)])))
var b = CALCULATE(MAX(Table1[Sales]),FILTER(ALL(Table1),[Date (English)]=MIN(Table1[Date (English)])))
Return
b-a

Result shown as below: 

pbix attached: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eb2Q_95HzQlJnYKDcTSoga4Bc2DcP08O-yPpH9tGW0NmjQ?e=hQn6yG

 

Best regards,

Dina Ye

Brysonds's avatar
Brysonds
Helper III
7 years ago

Hi v-diye-msft  Dina,

 

Thanks so much for taking the time to put this together. This gets me really close!

 

I tried using the formula you provided, and I get the following result. The variance is not calculating correctly


 

 

 

 

I tried altering the formula to this and it fixed the amounts, but has the wrong sign:


Variance =
var P1 = CALCULATE(MAX(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
var P2 = CALCULATE(MIN(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
Return
P2-P1

 

Here you can see that the variance for the first line is -27.20, but it should be positive (Jan - Feb asset amount).

 

 

 

 

 

 

Any ideas? 

 

Also, is there a way to hide the "variance" columns that show 0 and only show the variance sub-total?

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    7 years ago

    Hi Brysonds ,

     

    Take a try of this:

    Variance =
var P1 = CALCULATE(MAX(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
var P2 = CALCULATE(MIN(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
var P3 = CALCULATE(CSE[Fiscal Year / Period]),FILTER(CSE,CSE[Asset Amount]=P1))
var P4 = CALCULATE(CSE[Fiscal Year / Period]),FILTER(CSE,CSE[Asset Amount]=P2))
Return
IF(P3<P4,P1-P2,P2-P1)

    Regarding to the 0 in the matrix, we can't remove it currently. probably you can adjust the font color or other format to make it unconspicuous.

     

    Best regards,

    Dina Ye