Percent of total by month in Pivot table

AJ2020 · 2021-02-18T21:25:36+00:00

There was an error rendering this rich post.

AJ2020

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

Find more posts tagged with

Accepted answers

All comments

jaeW_at_Onyx

@AJ2020

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 ()

AJ2020

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

jaeW_at_Onyx

@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

https://www.mysqltutorial.org/mysql-window-functions/

sum(sum(amount)) over (partition by item, month)

AJ2020

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

user30972

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.

jaeW_at_Onyx

@user30972 , you are correct, mid Spring, Domo changed functionality so that PARTITION on calculated columns, month(SoldDate) no longer functions.