average (distinct fact.measure group by fact.fk_d)

Robert Angel

Due to limitations in a data model I have some measures that whilst they are correct in isolation, they are not correct when aggregated as they appear numerous times as the fact is a curious amalgam of tables.

Is there anyway in the rpd to create the equivalent of the following pseudo logic; -

average(fact.measure group by fact.fk_id)

and also

sum(fact.measure group by fact.fk_id)

The individual averages would be correct at a detail level as effectively they are coming from a header record, but the repitition of the header record across numerous detail lines would result in a skewed average as some have no repitition, some have numerous repitions.

As a simple example; -

                               Avg          Sum

Fact PK,  Fact FK, Measure  Measure

1              1         10000         10000

2              1         10000         10000

3              1         10000         10000

4              1         10000         10000

5              2         20000         10000

                       ------------------------

Average          15000

Sum                                   30000

                       ------------------------

                        

Average:   ((10000 + 20000) / 2) = 15000

Sum   (10000 + 20000) = 30000   

As it stands the standard functionality would yield 60000 / 5 = 12000 for the aggregated average and 60000 for the aggregated sum.

thanks for your input,

Robert.