Total Average - Monthly Average

[Cary_Casey]

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=

CALCULATE(SUM(table[product_quantity])/DISTINCTCOUNT('table'[YearMonth])

Unfortunately this provides a measure in the table just like monthly quantity.

Monthly Quantity=

CALCULATE(SUM(table[product_quantity]))

Difference=

[Total Average] - [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!

[VasTg · Accepted solution]

@Cary_Casey 

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 

[Cary_Casey]

@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!

[VasTg]

@Cary_Casey 

Glad it helped.

Connect in Linkedin