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

asanhueza

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.

marcel_luthi

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.

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

marcel_luthi

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.

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

Thank you! That worked!