# Pivot Table Issues - Needs Work

Options
Member
edited August 2023

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

Tagged:
2

#### Active · Last Updated July 2023

• Coach
Options

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.

• Domo Employee
Options

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

If this a suggestion above helps the customer resolve the issue on their end, please provide the outcome of this case for addressing any future issues that may arise.

• Member
Options

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

1. `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.