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:

 

CustomerSalesDate (English)
Alpha1001/1/2019
Alpha2002/1/2019
Alpha3003/1/2019
Alpha4004/1/2019
Alpha5005/1/2019
Alpha6006/1/2019

 

What I am trying to achieve:

 

1) Matrix visual with Company as Row, Date as Column, and Sales as Value

2) Slicer based on Date field

3) I see the variance in sales between any two months selected in slicer (i.e. Jan and Mar)

 

Desired output looks like this after I select Jan and Mar from slicer:

 

CustomerJan 2019Mar 2019Variance
Alpha100300-200

 

So in summary, I need to dynamically calculate the variance between two periods selected in a slicer.

 

Any ideas?

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Brysonds ,

     

    I just want to know if user select more than 2 selections in date slicer,

    or when user don't select any filters, how does the chart show?

     

    Aiolos Zhao

  • 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