Dynamic Percentages of Total
Hello! I am trying to get a percentage total column to work within a beast mode calculation in a pivot table card. I'm using the OVER () function, but because I have dynamic date ranges in the table (ex: we're looking at last 30 days only) it seems to aggregate by the grand total instead, not considering date ranges.
Is there a way to calculate a "dynamic" percentage of total, meaning that the %s will change based on the date range filtered?
Here's the formula I am using:
SUM(`field`)/ SUM(SUM(`field`)) OVER ()
Answers
-
I will defer to @jaeW_at_Onyx and @GrantSmith on this one as they are Window Function gurus. However, I believe you would need to add a partition by in your over clause, but you can't use MONTH('date') as Domo eliminated using a calculated field in the partition statement.
I would also suggest you watch Jae's video on window functions if you haven't already:
0 -
Hi @etszyingho
Your beast mode should automatically take into account your date filtering. Since you're using a pivot table things get a bit funky. How are you partitioning your data within your pivot table (what are you using for the columns)?
1 -
Hi @GrantSmith ! thanks for the tip. I actually had a column grouping on the pivot table as well, and so when I did not take that into account it did not aggregate correctly.
Once I added the partition on the column grouping after your suggestion I was able to get the correct aggregation.
Thank you so much for your response!
1 -
Window functions and pivot tables can be tricky. Glad you got it figured out!
1
Categories
- 10.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 337 Workbench
- 250 Domo Best Practices
- 11 Domo Certification
- 460 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 83 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 295 仲間に相談
- 649 ひらめき共有