Charting

Charting

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

image.png

Thanks,

Angela

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Answers

  • Coach
    edited February 2021

    @AJ2020

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


    grand total

    1. sum(sum(amount)) over ()


    to add subtotals by items

    1. sum(sum(amount)) over (partition by item)


    % of total would just be division

    1. sum(amount)
    2. /
    3. 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"
  • 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

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


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

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

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In