Percent of total by month in Pivot table

AJ2020
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

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀
    edited February 19

    @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
    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
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @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)
    
  • AJ2020
    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
    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
    jaeW_at_Onyx Budapest / Portland, OR 🟀

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

Sign In or Register to comment.