Sum of Average Sum

[Hanshans]

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!

[Anonymous]

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.

[Anonymous]

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].

[stevedep]

Hi,

I would suggest an AverageX for the inner average.

Kind regards, Steve.