cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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'

RETURN
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

``````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] ),
),
MonthInScope && not GroupInScope && not LocationInScope,
AVERAGEX(
DISTINCT( T[Group] ),
CALCULATE(
AVERAGEX(
DISTINCT( T[Location] ),
)
)
),
not ( MonthInScope || GroupInScope || LocationInScope ),
AVERAGEX(
DISTINCT( T[Month] ),
CALCULATE(
AVERAGEX(
DISTINCT( T[Group] ),
CALCULATE(
AVERAGEX(
DISTINCT( T[Location] ),
)
)
)
)
)
)
return
Result``````

Memorable Member

Hi,

I would suggest an AverageX for the inner average.

Kind regards, Steve.

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors