topic Re: Sum of Average Sum in DAX Commands and Tips

Sum of Average Sum

Hanshans — Thu, 18 Feb 2021 11:43:00 GMT

Hi experts

Ive had a read around the forum and not quite found what I'm looking for.

Example table of data below. The table shows headcount by site (column is actually called 'Full Name'), each site is in a group. I am trying to calcuate the total headcount for the month.

The steps involved to get total headcount are

1) find the average monthly headcount for each office / site ( 2nd table from the left on snippet)

2) sum the office/site averages to get headcount per group (3rd table from the left on snippet)

3) sum the group values to get total headcount (4th table from the left on snippet)

The dataset covers a number of months but I am using a relative date filter on the visual of 'in the last 1 calendar month' to filter down. The data set contains dates rather than month/year. month/year was in an attempt to simplify the example data

I have got as far as step 2 above using DAX formula

All Headcount by Group = SUMX(SUMMARIZE('All Headcount','All Headcount'[Full Name],"Average",AVERAGE('All Headcount'[Headcount])),[Average]

and adding this to a table visual alongside 'Group'

As expected the total of 1,427.19, 'provided' by the table is wrong so I tried to using the formula as a variable in a calculation using 'HASONEVALUE'

Test Sum Headcount Averages =

VAR HeadcountGroup = SUMX(SUMMARIZE('All Headcount','All Headcount'[Full Name],"Average",AVERAGE('All Headcount'[Headcount])),[Average])

RETURN

IF(HASONEVALUE('All Headcount'[Group]),

HeadcountGroup,

SUMX(VALUES(Headcount[Full Name]),HeadcountGroup))

However, this has given a total of 18,553.44 which is also wrong. The actual total is 1,680 (to 2 decimal points).

Can anyone help, where am I going wrong?

Relative newbie to DAX so very much learning as I go along

Thank you!

Re: Sum of Average Sum

stevedep — Thu, 18 Feb 2021 12:11:33 GMT

Hi,

I would suggest an AverageX for the inner average.

Kind regards, Steve.

Re: Sum of Average Sum

Anonymous — Thu, 18 Feb 2021 13:33:15 GMT

Hi Hanshans

First of all, you should clearly state the inputs. Can you therefore please describe (in the best way possible, maybe paste a picture) the table(s) you work with? We don't want to see 'a simplified view' of your data. We want to see your data. This is the first thing. You say "The data set contains dates rather than month/year." So, please show us the real thing, not some kind of simplification. If someone writes code that works with a 'simplification,' then the code may not work with the real thing. It'll be a waste of everybody's time.

Also, it's always good to state: the input, the transformation and the output. Best in the form of pictures as for the input and output. The explanation of transformation should be easily derived (by the reader) from the pictures but you can add your own words if you feel it's not clear enough.

You do the above and I'll try to figure something out with the info I already have.

Thanks.

Re: Sum of Average Sum

Anonymous — Thu, 18 Feb 2021 15:21:14 GMT

Here's your code:

AVG = var LocationInScope = ISINSCOPE( T[Location] ) var GroupInScope = ISINSCOPE( T[Group] ) var MonthInScope = ISINSCOPE( T[Month] ) var Result = SWITCH( TRUE(), MonthInScope && GroupInScope && LocationInScope, AVERAGE( T[Headcount] ), MonthInScope && GroupInScope && not LocationInScope, AVERAGEX( DISTINCT( T[Location] ), CALCULATE( AVERAGE( T[Headcount] ) ) ), MonthInScope && not GroupInScope && not LocationInScope, AVERAGEX( DISTINCT( T[Group] ), CALCULATE( AVERAGEX( DISTINCT( T[Location] ), CALCULATE( AVERAGE( T[Headcount] ) ) ) ) ), not ( MonthInScope || GroupInScope || LocationInScope ), AVERAGEX( DISTINCT( T[Month] ), CALCULATE( AVERAGEX( DISTINCT( T[Group] ), CALCULATE( AVERAGEX( DISTINCT( T[Location] ), CALCULATE( AVERAGE( T[Headcount] ) ) ) ) ) ) ) ) return Result

T[Location] is your T[Office/Site].