Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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!
User | Count |
---|---|
24 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
20 |