cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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=

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

## Re: Total Average - Monthly Average

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 3
Super User I

## Re: Total Average - Monthly Average

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

Frequent Visitor

## Re: Total Average - Monthly Average

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

Super User I

## Re: Total Average - Monthly Average

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.