Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Alpha | 200 | 2/1/2019 |
Alpha | 300 | 3/1/2019 |
Alpha | 400 | 4/1/2019 |
Alpha | 500 | 5/1/2019 |
Alpha | 600 | 6/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:
Customer | Jan 2019 | Mar 2019 | Variance |
Alpha | 100 | 300 | -200 |
So in summary, I need to dynamically calculate the variance between two periods selected in a slicer.
Any ideas?
Solved! Go to Solution.
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:
Best regards,
Dina Ye
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:
Best regards,
Dina Ye
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:
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?
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
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |