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)?
0 -
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!
0
