Dynamic Percentages of Total

etszyingho
etszyingho ⚪️
in Card Building

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

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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
    GrantSmith Indiana 🥷

    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
    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
    GrantSmith Indiana 🥷

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

Categories