cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Agaidar
Helper I
Helper I

How to get sum in Marix table subtotal for AVERAGEX?

Hi Team,

 

If there is any way to get subtotal result for my below example?

 

I do ROI impact of our promotions, but some of them can run multiple weeks and I need to find difference vs non promo/base weeks sales. This how I do Base Units sales Base Units = CALCULATE(AVERAGEX('1 Total Summary','1 Total Summary'[NON PROMO AVERAGE]),ALLSELECTED('1 Total Summary'))  

 

However, I would like to see sum instead of average in my subtotal result for base units/Matrix Table. 

 

Agaidar_0-1622057963719.png

 

 

   

1 ACCEPTED SOLUTION
Agaidar
Helper I
Helper I

Hi,

 

I think I nailed it

 

Base Units =
VAR AvgSales = CALCULATE(AVERAGEX('1 Total Summary','1 Total Summary'[NON PROMO AVERAGE]),ALLSELECTED('1 Total Summary'))

RETURN
IF(HASONEVALUE('Calendar'[Week M]),
AvgSales,
SUMX( VALUES('Calendar'[Week M]),AvgSales))
 
credits to Enterprise DNA

View solution in original post

1 REPLY 1
Agaidar
Helper I
Helper I

Hi,

 

I think I nailed it

 

Base Units =
VAR AvgSales = CALCULATE(AVERAGEX('1 Total Summary','1 Total Summary'[NON PROMO AVERAGE]),ALLSELECTED('1 Total Summary'))

RETURN
IF(HASONEVALUE('Calendar'[Week M]),
AvgSales,
SUMX( VALUES('Calendar'[Week M]),AvgSales))
 
credits to Enterprise DNA

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!