Pivot table total and subtotals are not the sum but the aggregation function

asanhueza
asanhueza Member
in Cards, Dashboards, Stories

I want my table to have the column aggregated as average while ensuring that the overall total corresponds to the sum. Currently, the Domo pivot table allows the column aggregation to be average but total and subtotal are also averages instead of sums.

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    With the default aggregations this won't be possible but you could achieve this behavior by using a beast mode instead. Here's an example where the first column is done by using AVG as the aggregation, while the second is done using the beast mode.

    image.png

    Keep in mind that this works fine with a 2 level row aggregation and you might need to modify it if you're using column breakdown, so make sure you include all the different levels of breakdown as part of the partition clause:

    SUM(AVG(`field`)) OVER (PARTITION BY `breakdown1`, `breakdown2`)

    So for example, if my Pivot table has the following
    Rows: Department, Employee
    Columns: Line
    Values: Sales

    Then the beast mode would be:

    SUM(AVG(`Sales`)) OVER (PARTITION BY `Employee`, `Line`)

    Hope this helps

Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    With the default aggregations this won't be possible but you could achieve this behavior by using a beast mode instead. Here's an example where the first column is done by using AVG as the aggregation, while the second is done using the beast mode.

    image.png

    Keep in mind that this works fine with a 2 level row aggregation and you might need to modify it if you're using column breakdown, so make sure you include all the different levels of breakdown as part of the partition clause:

    SUM(AVG(`field`)) OVER (PARTITION BY `breakdown1`, `breakdown2`)

    So for example, if my Pivot table has the following
    Rows: Department, Employee
    Columns: Line
    Values: Sales

    Then the beast mode would be:

    SUM(AVG(`Sales`)) OVER (PARTITION BY `Employee`, `Line`)

    Hope this helps

  • asanhueza
    asanhueza Member

    Thank you! That worked!

Categories