Data Model Value Average

[ChrisPBI]

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

[KGrice · Accepted solution]

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.