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 have a data set that lists sales for specific dates from 2017 through 2019. I created the following calculation for me to compare sales on a year-over-year basis:
Month End Date Sales Prior Year Sales
2/28/2017 $15,000,000 -
2/28/2018 $20,000,000 $15,000,000
I would like to add additional rows to the view above so I can see how individual groups performed on a year-over-year basis. As such, I added a data attribute called "Group" to break out sales by group. When I do this, however, I am noticing that the "Prior Year Sales" rows for 2/28/2018 lists the total sales for 2/28/2017, rather than the sales for each individual group. Here is an example of what I am seeing:
Month End Date Sales Prior Year Sales
2/28/2017
Group A $5,000,000 -
Group B $5,000,000 -
Group C $3,000,000 -
Group D $2,000,000 -
2/28/2018
Group A $6,000,000 $15,000,000
Group B $6,000,000 $15,000,000
Group C $5,000,000 $15,000,000
Group D $3,000,000 $15,000,000
Instead of seeing "$15,000,000" listed above in the "Prior Year Sales" column, I would like to see the sales for each comparable group listed.
With this said, I have two questions about this calculation:
1) Do I need to invoke the "Group" field in my calculation above in order for it to pivot properly?
2) If the answer to (1) is "Yes," is it possible to invoke 2+ fields in this calculation? Let's say that I want to pivot by "Group" and by "State."
Thanks for the feedback.
Solved! Go to Solution.
Hi @Anonymous ,
1) Do I need to invoke the "Group" field in my calculation above in order for it to pivot properly?
2) If the answer to (1) is "Yes," is it possible to invoke 2+ fields in this calculation? Let's say that I want to pivot by "Group" and by "State."
1) Yest.
2) We could invoke 2+fields in that calculation.
Please refer to my test sample. Here is the measure formula.
Measure = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group],'Table'[status]),SAMEPERIODLASTYEAR('Table'[Date]))
Here is the output.
Hope this can help your.
Best Regards,
Cherry
Hi @Anonymous ,
1) Do I need to invoke the "Group" field in my calculation above in order for it to pivot properly?
2) If the answer to (1) is "Yes," is it possible to invoke 2+ fields in this calculation? Let's say that I want to pivot by "Group" and by "State."
1) Yest.
2) We could invoke 2+fields in that calculation.
Please refer to my test sample. Here is the measure formula.
Measure = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group],'Table'[status]),SAMEPERIODLASTYEAR('Table'[Date]))
Here is the output.
Hope this can help your.
Best Regards,
Cherry
This worked, thanks, Cherry!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |