Pivot table total and subtotals are not the sum but the aggregation function
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
-
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
1
Answers
-
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
1 -
Thank you! That worked!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 103 SQL DataFlows
- 633 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 760 Beast Mode
- 62 App Studio
- 41 Variables
- 699 Automate
- 181 Apps
- 457 APIs & Domo Developer
- 51 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 400 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive