Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Pivot Table Data Manipulation
Nice work on V3, Vidas. I wonder if you or somebody might share some wisdom about pivot tables. I'd like to add the values in the columns of a pivot table to create a new column within the pivot table (or outside if it can be dynamic). The use case is this: I've used the symbol allocation in V3 and allocated my symbols percentage-wise to Cdn Equity, US Equity, Intl Equity, Fixed Inc, Other, and Cash. VBAL, for example, has a percentage in all. No problem creating a pivot table with Accounts as rows, the six Allocations as columns, and Total Value by Allocation in the data cells. But what I'd like to do is create a pivot table that aggregates Cdn Equity plus US Equity plus Intl Equity in to a new column called Total Equity. Also, I'd like to aggregate Fixed Inc plus Other into a new column called Fixed Income, and have a third column as simply the original Cash. The new pivot table would have the Accounts as rows but only three columns (Total Equity, Fixed Income, and Cash). I'd like to do this in a pivot table so I can use the slicers to filter on Portfolio, Currency, etc. I'm stumped on how to do this. I've tried to use the Calculate Field option under Fields, Items, & Sets, but it is greyed out. I presume this is somehow related to OLAP? Under the same menu item, I've tried to Create Set based on Column Items, but Excel crashes. I can't determine if the crash is due to Excel itself (32 bit 2106 Prof +) or the data file. I've even considered creating a new measure, but couldn't get past the aggregation aspect. So I've reached a dead end. Any thoughts?
Comments
Do not create sets, as they could SIGNIFICANTLY impact performance.
Unfortunately, I do not have time to fully understand what you want to do and create custom measure for you. But you can try building measures yourself. I would use SymboAllocation assignment of symbol and see who existing measures are calculated in "Allocation Actual %" measure.