Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cary_Casey
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
VasTg
Memorable Member
Memorable Member

@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 

Connect on LinkedIn

View solution in original post

3 REPLIES 3
VasTg
Memorable Member
Memorable Member

@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 

Connect on LinkedIn

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

 

@Cary_Casey 

Glad it helped.

 

Connect in Linkedin 

Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.