Averaging a product of values

[masplin]

Hi. looknig for them ost efficient way to do the following. I have users carrying out spending transactions and want to know the average no of spends per user in different catagories e.g. male/female. I can think of 2 ways:

1. In powerpivot window on the user table do SpendCount=CALCULATE(counta(users[transactions]),filter(transactions,transaction[transaction]="spend"))

Then in my pivot table do an average of Spendcount.
2. Create a pivottable measure AvSpendcount=CALCULATE(counta(users[transactions]),filter(transactions,transaction[transaction]="spend"))/
CALCULATE(DISTINCTCOUNT(Users[UserID]),filter(transactions,transaction[transaction]="spend"))

2nd one has the advantage of not creating yet another column of data in my model but means I can't just reuse Spendcount in other calculations. Is there any other smart way tod this sort of thing as doing it a lot i.e. user aveage of some calculation?

Thanks

Mike

 

[AlbertoFerrari]

If you do this with a calculated column, then you will not be able to perform calculations in different time periods, which is a pity. Your SpendCount is computed per user and fixed in the database... I would not do this way. I have not tried this code, but this might be a simple idea:

AVERAGEX (Users, CALCULATE (COUNTROWS (Transactions), Transactions[Transaction] = "Spend"))

You need to decide how to handle users who never bought anything, do they count as zero or do you want to ignore them?

Alberto
SQLBI

 

[masplin]

Ah didnt know there was an AVERAGEX function only SUMX!!! I'll give that a try