Percent of total by month in Pivot table
Hi,
I have a pivot table card that shows the units and $ for products sold each month. How do I show the percent of the total $ that product is for that month or the total column? (see highlighted below).
Rows: Region, Product
Columns: SoldDate
Values: SUM of Units, SUM of Price
Thanks,
Angela
Answers
-
you need window functions enabled in analyzer! Talk to your CSM.
grand total
sum(sum(amount)) over ()
to add subtotals by items
sum(sum(amount)) over (partition by item)
% of total would just be division
sum(amount) / sum(sum(amount)) over ()
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Hi @jaeW_at_Onyx ,
Thanks. I had windowed functions enabled after watching your great video on Open Rate https://youtu.be/ZPf41Fjn1H8 😀, but I was having challenges with getting the formula to work with my case. I applied the formula above, but the monthly product rows do not add up to 100%. The grand total row at the bottom of the months are Dec: 39%, Jan: 36%, & Feb: 25%, which adds up to 100% in the total column, but I would like each month to be 100%. My date range filter is "Last 3 months, by month".
Thanks,
Angela
0 -
@AJ2020 so... instead of having the denominator be the grand total by item you want to subtotal by item and month
Window functions are not unique to Domo. It's a common SQL thing. Here's some documentation so you can understand syntax
sum(sum(amount)) over (partition by item, month)
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi @jaeW_at_Onyx ,
Thank you for your reply and additional documentation. I am still having challenges getting it to work. I tried the new function, but it switched all of the units to "1". I tried switching the order of the partition by fields but no luck. The SQL link discussed frame-clauses. Is that needed? Below are the results with 2 different functions. What am I doing wrong?
Thanks, Angela
0 -
Having just had a similar problem, it seems like the partition statement works better when used with a field on the data set, rather than a calculation. Try using a data flow to create a column called SoldMonth that is equal to MONTH(`SoldDate`), and use that in the partition statement instead.
0 -
@user30972 , you are correct, mid Spring, Domo changed functionality so that PARTITION on calculated columns, month(SoldDate) no longer functions.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- 10.5K All Categories
- 5 Connect
- 915 Connectors
- 250 Workbench
- 459 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 186 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive