•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Trying to create a variance column in pivot table - AutoSort and AutoShow causing issues?
Our take
Struggling with variance calculations in your pivot table can be frustrating, especially when familiar methods suddenly encounter roadblocks. The error message saying "AutoSort and AutoShow can't be used with custom calculations that use positional references" highlights a common issue when transitioning between datasets. This guide will unravel the reason behind this problem and offer practical workarounds to maintain both sorting and variance calculations. By the end, you'll have a clearer path to achieving the insights you need without sacrificing functionality.
I've previously made a pivot table that has: rows for customer, columns for year, revenue for values, and is sorted by revenue largest to smallest.
Previously, I was able to create another set of columns for variance (percentage), by dragging "revenue" into values, and changing the column to "Show Values as % Difference".
Now, I'm using a different dataset (also from Power Query), trying to do the same thing, but running into this problem:
I get an error message, "AutoSort and AutoShow can't be used with custom calculations that use positional references. Do you want to turn off AutoSort/AutoShow?" If I say yes, I can't sort anything (which is essential), if I say no, I can't calculate variance (also essential).
All the fields are the same, but I have a filter for different salespeople here (but it still doesn't work when I remove this filter.) I can't figure out why this is happening on this pivot table, when it worked perfectly before, any ideas or workarounds?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]
- Pivot Calculated Field issueHi guys, Im still learning excel and im having a hard time inserting a formula in a pivot table that initially created in a manual table. I wanted to to utilize the pivot function of opening a new sheet with the filtered values + the formulas that I want to show. Here's a link to the sample data set that i'm using. Here's the breakdown: have 200 transactions over a 3 day period (no dates for this one sadly) with a sudden decline in approval rate divided it into 2 groups (first half, last half) Wanted to make a pivot table checking the approval rates of each of the following: Card type, Country, MID (merchant ID). This is just to investigate the root cause. I already have a sample formula for the Card type part but having trouble inserting it in a pivot table. It should be easy to apply to other pivot tables once I get this one done =(COUNTIFS(Raw!H:H,"First 100",Raw!B:B,"Approved",Raw!D:D,"Amex")/COUNTIFS(Raw!H:H,"First 100",Raw!D:D,"Amex") submitted by /u/NierraSol [link] [comments]
- How can I create a pivot table with the PowerPivot Measure being used for the rows?Building an inventory valuation model. Basically, taking data from sales, warehouse inventory, and stores to find lower of costs/sales value. I'm also applying a obsolescence formula that basically just says "if we sold 12.5% of our all available units over the year, then mark it as current, otherwise mark it as the last date we received it in the warehouse". This measure is named "Slow moving date". I'd like to use "slow moving date" as a row in a pivot table, but Excel says that value can't be a row. How can I force it to allow the dates as a row without having to copy/paste numbers only from a different pivot to make it work? submitted by /u/scaredycat_z [link] [comments]
- Sorting pivot table column headingsHey fellow Excellians! Been having some major frustrations trying to keep my pivot table column headings in the right order. Column headings are weekbands: 0–2 Weeks 2–4 Weeks 4–9 Weeks 9–13 Weeks 13–21 Weeks 21–26 Weeks 26–36 Weeks 36–52 Weeks 52–62 Weeks 62–78 Weeks 78+ Weeks Total 2 Weeks 9 Weeks 13 Weeks 52 Weeks So far I’ve tried: Using custom sort lists Sort by > Column in the Data Model Protecting the worksheet Regardless, any time I apply a filter from a slicer (which will sometimes remove columns from the pivot as there is no data), all of the column headings get jumbled. Not sure if there is something simple I’m missing. I also want to “lock” the columns/rows in place even when no data is returned from slices applied so that the table structure remains the same, but thought that fixing the sort issue would come first. Maybe I’m wrong? Thanks! submitted by /u/irish_shitlord [link] [comments]