4 Replies Latest reply on Jan 16, 2019 10:13 PM by Tushar More

    Averaging on conditional count distinct

    Hayley Price

      Hi all,

       

      I have a table with payment details in (rows and rows of transactions with dates, amounts and types in there) and a table with member information in. The two are joined via "Member Number" on a right join to the Member Table. The aim of the game is to get an average of transaction amounts by the distinct count of members per month (so what is the average transaction amount, per month, per member, to try and get a "value" of a member) to be filtered by "Transaction Type" in the end.

       

      From previous research I have done this:

       

      SUM({FIXED [Account Id] : AVG([Contribution Amount])})

      /

      COUNTD ([Account Id])

       

      However, this doesn't appear to be working too well as when I start to filter is seems to only filter by the number of members making that transaction, when really I want it for the whole count of membership in that month, regardless of if they made that transaction type of not.

       

      For example for Dec 18 the average for all transactions is $1500 per member, (there are approx 3000 members, increasing each month, hence it needs to be dynamic and look at the total for that month) when I filter to "lump sums" this goes to $24k. which isn't right as I know the total in 'lumps sums' for Dec 18 was $1m, so the answer should be more like $333). I feel like the above calc needs to reference the month or something but my knowledge is not good enough to work this out.

       

      I'm very sorry but I can't share my work book (I know it is a heck of a lot easier when people do share) as it's sensitive data. Though I can try and mock one up if this is tricky to answer based from my description.

       

      Thank you in advance!!