Avg in a fact table

[ilanbaruch]

hi all

in my model i have a fact table with date, id and event types

in addition to that i have a field that calculates the day dif between min date and max date of every id in the fact.

this filed (day dif) is static and is showing the same value for all rows for each id.

in UI i want to calculate AVG of the day dif , but some id's have more sows then others in the fact . 

what would be the best way to achieve this ?

[Gysbert_Wassenaar · Accepted solution]

Try Avg(TOTAL [day dif]). The TOTAL will ignore any chart dimensions so you should get the average calculated over all rows.

[crusader_]

Hi,

Just for the sake of curiosity what's the point of calculating AVG of static value? 

---

@ilanbaruch wrote:

this filed (day dif) is static and is showing the same value for all rows for each id.

---

In this case AVG will always be equal to this static value disregarding of number of occurrences of this value.

May be you wanted to calculate slightly different thing?

//Andrei

[ilanbaruch]

it is static for each id , the avg is for all id's in the fact

[ilanbaruch]

Thank you