Dynamic Percentages of Total

etszyingho · 2021-09-23T15:08:15+00:00

There was an error rendering this rich post.

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 ()

Find more posts tagged with

Table

Accepted answers

All comments

MarkSnodgrass

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:

https://www.youtube.com/watch?v=eifSYZIcPzg

GrantSmith

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

etszyingho

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!

GrantSmith

Window functions and pivot tables can be tricky. Glad you got it figured out!