Average of Totals

Anything related to PowerPivot and DAX Formuale
MFCL
Posts: 3
Joined: Fri Apr 05, 2019 1:37 am

Average of Totals

Postby MFCL » Wed Apr 10, 2019 9:04 pm

Hi,
I am trying to aggregate the totals in between months here using this expression. However, I am not able to obtain the value I am looking for.

AvgeWgtMo%:=IF (
HASONEVALUE ( dCalendar[Month Name] ),
[Weight%],
SUMX( VALUES ( dCalendar[Month Name] ), [AverageWgt%] )
)

The average of totals should be something like 99.8#%
Could you please help?
Data Sample.xlsx
(501.96 KiB) Downloaded 5 times

MattAllington
Posts: 1036
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Average of Totals

Postby MattAllington » Sun Apr 14, 2019 9:52 am

How about this.

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

=
AVERAGEX (
    VALUES ( dApplications[SLR] ),
    SUMX ( VALUES ( dApplications[APPID] ), [AverageWgt%] )
)


Read my article about SUM vs SUMX It also applies to AVERAGEX
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

MFCL
Posts: 3
Joined: Fri Apr 05, 2019 1:37 am

Re: Average of Totals

Postby MFCL » Mon Apr 15, 2019 11:42 am

Hi Matt,
Thank you for the response. The expression you indicated works perfectly for the Individual months, calculating the averages in between the APPIDs. However, at the aggregate level, it does not give me the average between the months.
January Total = 99.904
February Total = 99.294
Grand Total should be 99.599 and the expression gives 99.938

Any suggestions?

Thank you so much for your help. I read the article... Will do it again to see if I can figure out how to include this context.

PhilC
Posts: 219
Joined: Tue Sep 09, 2014 8:13 am

Re: Average of Totals

Postby PhilC » Thu Apr 18, 2019 8:29 pm

Hi MFCL,

The issue you are experiencing is that you are expecting the Grand Total to be the average of the monthly averages (ie other calculated cells), where it is actually the average of all the records (ie no month filter). There are a lot of blog posts that work through examples of this and explain how the DAX engine is actually calculating each cell individually, without any use of the other cell results.

This video should help you along if you actually want the Total to be the Average of the Months, not the genuine average across all months - https://www.youtube.com/watch?v=XfUe915hH7w
It is showing a SUM but you should be able to use the same approach for the AVERAGE.

Cheers
Phil


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron