Forum Discussion
Total Average - Monthly Average
Hello,
I'm attempting to show a difference between average product over all history minus the monthly sum product. The problem is getting a measure that ignores the dates and just takes an aggregate average inside a table. The monthly average is already calculated just fine. The difference is already set up and trivial. One sticking point is Average Monthly Quantity still needs to be filtered by other slicers. Just not affected by date in that table and other visualizations.
Average Monthly Quantity=
Unfortunately this provides a measure in the table just like monthly quantity.
Monthly Quantity=
Current Outcome:
| Year/Month | Average Monthly Quantity | Monthly Quantity | Difference |
| 2020/1 | 1377211 | 1377211 | 0 |
| 2019/12 | 8052345 | 8052345 | 0 |
| 2019/11 | 1797383 | 1797383 | 0 |
Desired outcome:
| Year/Month | Average Monthly Quantity | Monthly Quantity | Difference |
| 2020/1 | 5514051 | 1377211 | 4136840 |
| 2019/12 | 5514051 | 8052345 | -2538294 |
| 2019/11 | 5514051 | 1797383 | 3716668 |
Ideally I'd also like to put this into a scatter plot to visualize the deviations. Any assistance is much appreciated!
Try this..
Average Monthly Quantity=CALCULATE((SUM(table[product_quantity])/DISTINCTCOUNT('table'[YearMonth])),ALL(table))If it helps, mark it as a solution
Kudos are nice too
3 Replies
Try this..
Average Monthly Quantity=CALCULATE((SUM(table[product_quantity])/DISTINCTCOUNT('table'[YearMonth])),ALL(table))If it helps, mark it as a solution
Kudos are nice too
VasTg Thank you!
It does indeed include all dates! I appreciate your prompt response. I was really not wrapping my head around it clearly!
Edit: Changed the code to AllExcept so that I could use the slicers that apply to this data!
