![]() Using an OLAP PivotTable based on the DataModelĬalculating percentage point increases likely requires using PivotTables built using the Data Model. Of course, you could always use the GETPIVOTDATA function to do the additional math for you like we did earlier, like I've done on that left hand side. If you want to show say change in market share from one year to the next, then percentage points make sense. But I guess it depends on what you want to show. That said, I think percent increase is less confusing. I say kinda, because without using external formulas (or without calculating the percentages back in your source data), you can only get it to show percent increases (see far right column), not percentage point increase like you want (see far left column). You can kinda solve your problem entirely within a self contained 'Traditional' PivotTable if you drag the Amount column to the Values area, put the Year column in the Columns area, put your Competitors in the Rows area, and choose one of the percentage Show Values As options you'll see when you right-click a cell in the Values area. Then you can additionally do the following: That said, you can still use the GETPIVOTDATA function on your existing (unpivoted) data layout in a way that is somewhat more robust to changes in your PivotTable structure than just subtracting one reference from the other:īut again, I recommend transforming your data into a Flat File. To transform your data into a flat file, see my answers at convert cross table to list to make pivot table Instead, you really want your data laid out so that you have a column called Amount and a column called Year, and then you can use the Show Values As options available from the right-click menu to show as percentage differences across years. Basically, PivotTables aren't meant to consume cross-tabulated data. your source has columns called Year 1, Year 2), and the type of percentage comparisons across years you want to do doesn't work if your data is a crosstab. Currently you're using a cross-tabulated data source (i.e. ![]() In both cases, I recommend that you unpivot your data first, so that it is in what's known as a Flat File. ![]() This will give you both percentage differences and percentage point differences, without having to resort to using external formulas. Use a 'OLAP' PivotTable that's based on data you've added to the Excel Data Model.This will give you percentage differences, but you can't get percentage point differences like you're asking for without using external formulas. Use a 'Traditional' PivotTable that's based on a range. ![]() If trying to solve this with PivotTables, you've got a couple of options: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |