cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Cary_Casey Frequent Visitor
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/MonthAverage Monthly QuantityMonthly QuantityDifference
2020/1137721113772110
2019/12805234580523450
2019/11179738317973830

 

 

Desired outcome:

Year/MonthAverage Monthly QuantityMonthly QuantityDifference
2020/1551405113772114136840
2019/1255140518052345-2538294
2019/11551405117973833716668

 

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

Re: Total Average - Monthly Average

@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 

View solution in original post

3 REPLIES 3
Super User I
Super User I

Re: Total Average - Monthly Average

@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 

View solution in original post

Cary_Casey Frequent Visitor
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
Super User I

Re: Total Average - Monthly Average

@Cary_Casey 

Glad it helped.

 

Connect in Linkedin 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors