cancel
Showing results for
Search instead for
Did you mean:
New Member

## Aggregate over groups dynamically

I have a table that looks like this:

The end result would need to look like this:

The table result will change based on other filters selected, so I don't think it can be a calculated column. A measure makes more sense, but have struggled trying to figure out how to sum the data dynamically based on filters selected. Any assistance is appreciated! Thank you.

3 REPLIES 3
Super User IV

Unless I am missing something, you should just be able to do something like:

```Total Cost = SUM([Cost])

Total Quantity = SUM([Quantity])

Cost Per = DIVIDE([Total Cost],[Total Quantity],BLANK())```

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

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

Proud to be a Super User!

New Member

Thank you for the suggestion. I tried it and it gave the same results as my calculation below, but both are still not quite the right answer. It needs to be the summation of all costs / the summation of all quantities averaged over all locations depending on the filter selected.

Value =
var __Numerator = CALCULATE(sum(Table[Cost]),FILTER(Table, Table[Location]=SELECTEDVALUE(Table[Location])))
var __Denominator = CALCULATE(sum(Table[Quantity]),FILTER(Table, Table[Location]=SELECTEDVALUE(Table[Location])))

return DIVIDE(__Numerator/__Denominator)

Anonymous
Not applicable
```[Total Cost] = sum ( 'Table'[Cost] )
[Total Quantity] = sum ( 'Table'[Quantity] )
[Average Cost] = divide ( [Total Cost], [Total Quantity] )

[Your Measure] =
averagex(

addcolumns(
summarize(
'Table',
'Table'[Location]
),
"'Table'[AvgCostPerLocation]", [Average Cost]
),

'Table'[AvgCostPerLocation]
)```

Please never use the table name in front of a measure and always use the table name before a column. This is one of the most important things in good DAX. Never deviate from this. EVER.

Best

Darek

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors