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
