none
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

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.

     

     


    Tuesday, July 29, 2014 3:00 PM
    Answerer

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
    Answerer
  • 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
    Answerer