# 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

• Coach
edited February 2021

you need window functions enabled in analyzer! Talk to your CSM.

grand total

sum(sum(amount)) over ()

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"
• 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

• Coach

@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"
• 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

• 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.

• Coach

@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"