Hello all,
I've created a pivot-table summarising a worksheet of 38k rows by category across years.
I've added a % column, but I really only want this for the total-count in the last column of the pivot-table.
Is there a way to include this, but exclude the % columns for each year?
Thanks, Chris.
Don't think that's possible with regular Pivot Table. Have even looked at Power Pivot with a DAX measure. you can suppress the yearly percentages but the column will still appear. Couldn't find a solution for not showing the column at all. Perhaps someone else does.
But, in case you need a quick fix, consider hiding the yearly % columns.
If you use Power Pivot, you can do it by creating a column set. Assuming the years will change in the future, you'll either need to edit the set when the years change, or edit the MDX for the set so that it automatically picks up all years. The former is easier, but you need to remember to do it when the data changes!
Hi Riny and Velouria, thanks for your replies.
I didn't realise you can hide columns in pivot-tables, that's a good enough solution for my needs.
Although having now hidden the % column for each of the 9 years, when I then select all the remaining columns to make them a little wider, all the % columns return! Playing around with column-widths, it appears that hiding a column simply reduces the column width to zero, such that you can grab the edge of the column in the row with the A,B,C,D etc. labels, and pull the column back in to view - annoying!
I've not used power-pivot at all, may be above my pay-grade, but I'll bear that in mind for future exploration.
Thanks again, Chris.
You might find it easier to move the Values field above the Year in the Column field area. That way, all the hidden columns will be together at the end of the pivot table and should make it less of a hassle to resize the others.
For something like this, Power Pivot is pretty simple as you don't even need to write any measures. You'd just create the pivot table layout the same way you did (you just initialise the pivot table from the PP window), but you'd then have the option to create a column set - basically just a list of which columns you want displayed in the pivot table.
Thanks for the additional comments, Velouria, I'll see what I can do with those.