Pivot Table Issues - Needs Work

Mickey · 2023-07-10T23:00:18+00:00

There was an error rendering this rich post.

Hi there,

I've been using pivot tables for a while, and I just noticed a weird way in which Pivot Table cards specifically sort their data. I have the retail sales of a bunch of different brands, and then I sort them by month using a pivot table. When I go to sort them or filter by retail sales, it sorts or filters based on the MONTH that those retail sales happened.

Example: I have two brands that have sales for January through April.

Brand 1: $125 - Jan, $80 - Feb, $215 - Mar, $15 - Apr || Total - $435

Brand 2: $250 - Jan, $40 - Feb, $100 - Mar, $30 - Apr || Total - $420

SORTING ISSUE: If I sort by total retail descending, in the pivot table, Brand 2 is now above Brand 1 because it made $250 in January (more than Brand 1 in the same month). From what I can find, there is no way to sort by the true total unless you click the sorting arrows within the total column that the card can generate. If I have another card that produces the This Year vs Last Year retail sales as a percentage, where there is no Retail total column to sort by, this means I will never be able to sort by the true Retail total, only whichever brand made the most in ONE of the months given.

FILTER ISSUE: If I apply a filter to the card, Retail > $100, the Pivot table marks any month with less than $100 as $0, but keeps any months that made over that. So Brand 1 goes from $435 to $340 because it made over $100 in Jan & Mar, but not Feb & Apr (it puts $0 for those months). For Brand 2 the total would be $350 for the same reasons as Brand 1, and now the total is saying it made MORE than Brand 1 because of the filter. I don't want the filter to apply to months, just the total amount.

I understand this is a long explanation for a very specific fix, but it is an issue that will be encountered a lot in the future for us and there seems to be no way around this problem. It's an issue within the card itself that we cannot fix

Find more posts tagged with

Cards

Table

Submitted and awaiting review from product team

Status: Submitted and awaiting review from product team

Comments

marcel_luthi

Hi, I haven't tried yet, but this seems to be something you could workaround by creating a beast mode using the fixed function, that will return you the true total by brand, something like:

SUM(SUM(`Retail`) FIXED (BY `Brand`))

And then using this beast mode for both the sorting and filter criteria. Might be worth giving it a shot.

AKnowles

@Mickey If you look at the Knowledge article they have on Pivot Tables, this is a known issue I believe:

https://domo-support.domo.com/s/article/360043429473?language=en_US

You are supposed to use the sorting arrows when you are using a pivot table.

Mickey

UPDATE: I was using the below formula as a sort and filter, which was working for a while. However, I'm now encountering a strange issue that will not let me save the card if I have this formula applied as a filter (it was working before). It works as a sort, it works if I put the beast mode into the pivot table as a value, but once it is in the filter the card will not save. To clarify, the card itself accepts the filter and works within the card editor. I can see the card with the filter applied and all values look good. It is only when I try to save the card that I get the message: "There was a problem saving your card. If the problem persists, contact the Domo support team."

SUM(SUM(`Retail`) FIXED (BY `Brand`))

I think there was an update to Domo which does not allow the FIXED function to be in filters. Why is that? I can see it work perfectly fine within the card editor. It's as if Domo is just being petty with this one issue. This was the only way to filter how I needed to filter, and now it does not work.