Answered by:
Need DAX to do sum/average

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
Question
Answers

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.
 Edited by Mike DietterickEditor Tuesday, July 29, 2014 3:02 PM typo
 Marked as answer by Moyz Khan Tuesday, July 29, 2014 8:22 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] ) )
 Edited by Paul TurleyMVP, Moderator Sunday, July 27, 2014 6:14 AM Added closing parentheses to SUM function in both examples
 Proposed as answer by Paul TurleyMVP, Moderator Sunday, July 27, 2014 6:15 AM


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

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.
 Edited by Mike DietterickEditor Tuesday, July 29, 2014 3:02 PM typo
 Marked as answer by Moyz Khan Tuesday, July 29, 2014 8:22 PM