Weighted Average Price Beast Mode
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
-
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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()
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 469 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive