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.
Hello everyone, I am trying to create 2 matrixes. For the first matrix, I want to look at the total sum for each user by month/year divided by the number of stores they are responsible for, giving me the avg monthly total per store. (total/number of stores)
For the second matrix, I want to look at a rolling 12-month moving average for a user divided by the number of stores they are responsible for, giving me the moving 12-month avg per store. (rolling 12-month avg/number of stores)
So, it feels like a simple solution but I am having an issue with calculating a rolling sum. If I can calculate the rolling sum, dividing by the number of stores will give me a user's monthly spend by store. From there I believe to get the moving 12-month average, I could divide by 12 or use the datesinperiod expression.
Here is the rolling sum formula with errors:
I'm not sure why I am not seeing a rolling sum on the first matrix when the formulas are clearly different.
Thank you in advance!
Solved! Go to Solution.
Hi @james_pease,
It seems like a common issue when measure expression calculates with multiple aggregates, you can take a look at the following blog to know how to handle this scenario:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
I think I might have a solution, I am double-checking the data manually to see if the numbers add up:
Hi @james_pease,
It seems like a common issue when measure expression calculates with multiple aggregates, you can take a look at the following blog to know how to handle this scenario:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Thank you, I was hoping to avoid using Summarize as I will lose filter context but it appears this would be the best solution. Thank you so much!
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 |
---|---|
46 | |
28 | |
23 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
45 | |
16 | |
12 |