Weighted Average Price Beast Mode

jgillman
jgillman ⚪️
in Card Building

Hi Everyone,

I am working on creating a beast mode that will calculate a weighted average price of subscriptions. Essentially, the formula will take the subscription price and multiply it by the number of subscriptions with that price, then divide by the total number of subscriptions.

This is what I have so far.

sum(count(`subscription_id`) * `price`)/count(`subscription_id`)

Love to get some help! I don't want to use a "case" statement because there are over 600 prices that I am working with in my dataset.

Thanks

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @jgillman

    You'll need to aggregate your data and count all of the subscriptions before attempting to calculate the weighted average. You can't aggregate an aggregate in a beast mode so pre-aggregation is required.

    Once you have the number of subscriptions counted you can then use that new field in your beast mode to calculate the weighted average



    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    If you don't have window functions in beast mode enabled, you will need to get that enabled by your CSM. Then, if you add the OVER() clause to your denominator, you will be able to get a count of your total number of subscriptions. Like this:

    sum(count(`subscription_id`) * `price`)/count(`subscription_id`) OVER()




    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Categories