# Need DAX to do sum/average

### Question

• I have table where the amount reports and its the same amount but with different ID . Example below

ParentID    Amount      ID   Detail Amount

1             100.00        1      10.0

1             100.00        2      10.0

1             100.00        3      10.0

1             100.00        4     10.0

Something like this

Sum(Amount) / Count(ParentID)  = 500/5 = 100  and this behavior repeats for every parentID . In sql it would be

SELECT ParentID , Sum(amount) / count(*)

FROM Table

Group by ParentID

Friday, July 25, 2014 10:56 PM

• COUNTROWS() will filter by ParentID if ParentID is in the pivot.  Measure results always get filtered by what is showing in the pivot unless you specify otherwise in the measure.

This is the measure and it will only work when placed in a pivot table:

ParentIDAverage:=
SUM(TableName[Amount])/COUNTROWS(TableName)

I'm not sure what Amount field you want to use since you have 2 showing

So with that data set and using Parent Amount, if you place ParentID in the Pivot Table rows and this measure in Values, it will do this calculation for ParentID 1: 500/5 = 100

If you use Detail Amount, it would be: 100/5 = 20

This is the calculated column and it is meant to work in the actual Power Pivot table:

=CALCULATE(
SUM(TableName[Amount])/
COUNTROWS(TableName),
ALLEXCEPT(
TableName,
TableName[ParentID]
)
BTW, thanks to Paul for correcting my typo.

Tuesday, July 29, 2014 3:00 PM

### All replies

• As a measure:

ParentIDAverage:=
SUM(TableName[Amount])/COUNTROWS(TableName)

As a calculated column:

=CALCULATE(
SUM(TableName[Amount])/
COUNTROWS(TableName),
ALLEXCEPT(
TableName,
TableName[ParentID]
)
)

Saturday, July 26, 2014 8:36 PM
• Will CountROws(tableName) count all the rows and not just rows for a ParentID ?
Tuesday, July 29, 2014 2:40 PM
• if the data set was

 ParentID Parent Amount Detail Id Detail Amount 1 100 1 20 1 100 2 20 1 100 3 20 1 100 4 20 1 100 5 20 2 25 6 10 2 25 7 10 2 25 8 5

Wanted to sum up the parent amount without counting it that many times for Parent Id =1 Sum = 100 and for Parent Id = 2 Sum = 25

Tuesday, July 29, 2014 2:50 PM
• COUNTROWS() will filter by ParentID if ParentID is in the pivot.  Measure results always get filtered by what is showing in the pivot unless you specify otherwise in the measure.

This is the measure and it will only work when placed in a pivot table:

ParentIDAverage:=
SUM(TableName[Amount])/COUNTROWS(TableName)

I'm not sure what Amount field you want to use since you have 2 showing

So with that data set and using Parent Amount, if you place ParentID in the Pivot Table rows and this measure in Values, it will do this calculation for ParentID 1: 500/5 = 100

If you use Detail Amount, it would be: 100/5 = 20

This is the calculated column and it is meant to work in the actual Power Pivot table:

=CALCULATE(
SUM(TableName[Amount])/
COUNTROWS(TableName),
ALLEXCEPT(
TableName,
TableName[ParentID]
)
BTW, thanks to Paul for correcting my typo.

Tuesday, July 29, 2014 3:00 PM