Forum Discussion

ChrisPBI's avatar
ChrisPBI
Advocate III
10 years ago
Solved

Data Model Value Average

Hello,

 

my data model says that a product can have emissions in four life cycle stages.

 

Example for product XY with A to D as the life cycle stages as table values:

 

Product    Life Cycle Stage   Value

    XY                   A                    10

    XY                   B                    20

    XY                   C                     5

    XY                   D                   15

 

Therefore each product has four rows in the relationship table.

 

When I want to calculate the Average of all emissions (values) the answer in this example

should be 50 since there is only one product. But it is calculated 50 / 4 =12,5.

 

Do I have to change the underlying tables or why is the calulation wrong?

 

Thanks and Regards,

Chris

Need Help
  • KGrice's avatar
    KGrice
    10 years ago

    Hi ChrisPBI. If you use =AVERAGE(TableName[Value]), you'll get 12.5. To get the sum of the value column divided by the number of products, you could use this measure:

     

    Measure = SUM(TableName[Value]) / DISTINCTCOUNT(TableName[Product])

     

    For your sample, that should evaluate to 50 / 1 = 50.

1 Reply

  • KGrice's avatar
    KGrice
    Memorable Member
    10 years ago

    Hi ChrisPBI. If you use =AVERAGE(TableName[Value]), you'll get 12.5. To get the sum of the value column divided by the number of products, you could use this measure:

     

    Measure = SUM(TableName[Value]) / DISTINCTCOUNT(TableName[Product])

     

    For your sample, that should evaluate to 50 / 1 = 50.