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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hanshans
Helper I
Helper I

Sum of Average Sum

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. 

DAX help.PNG

 

 

 
 

 

 
 

 

 

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'
DAX help 1.PNG
 

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!
 

 

 

 

 

 
 

 

 

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

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
Memorable Member
Memorable Member

Hi,

 

I would suggest an AverageX for the inner average.

Kind regards, Steve. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors