Weighted average in Fact table

[cristian_av]

Hi

I have a fact table with the stipulated payment days of customers. These days are stipulated for each operation. I have the operations in a fact table and each operation can be repeated multiple times depending on the keys of dimensions...

I need to get this number:

Avg PaymentDays=(10*2+20*1+30*1)/4

Fact Table
Customer	Operation	PaymentDays	Key1	KeyN…
A	A	10	x	…
A	A	10	x	…
A	B	10	x	…
A	C	30	x	…
A	C	30	x	…
A	C	30	x	…
A	D	20	x	…
A	D	20	x	…
A	D	20	x	…
A	D	20	x	…

A resume of the fact table is like this (Just for you to understand the calculation)

Days	Operations (Count)
10	2
30	1
20	1

How should I do this calcution?

Thanks!!

[sunny_talwar]

May be this

Sum(Aggr(PaymentDays * Count(DISTINCT Operation), Customer, PaymentDays)/Count(DISTINCT Operation)

[cristian_av · Accepted solution]

Didn't work! It gives me a "-" as result

This expression worked:

Sum(PaymentDays * Aggr(Count(DISTINCT Operation), PaymentDays )) /

Count(DISTINCT Operation)

Thanks!