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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

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!

3 REPLIES 3
Anonymous
Not applicable

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

Memorable Member

Hi,

I would suggest an AverageX for the inner average.

Kind regards, Steve.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors