cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brysonds Member
Member

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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: 

2.PNG

pbix attached: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eb2Q_95HzQlJnYKDcTSoga...

 

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

4 REPLIES 4
AiolosZhao Established Member
Established Member

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

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

Community Support Team
Community Support Team

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

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: 

2.PNG

pbix attached: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eb2Q_95HzQlJnYKDcTSoga...

 

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Brysonds Member
Member

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

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


variance2.JPG

 

 

 

 

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).

 

variance.JPG

 

 

 

 

 

Any ideas? 

 

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

Community Support Team
Community Support Team

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

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)