Percent of total by month in Pivot table

AJ2020
AJ2020
in Charting

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

image.png

Thanks,

Angela

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited February 2021

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

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


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

    image.png

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

Categories