Hi,
- I do know and understand that calculated items only work under the same field/column.
- If the column in the pivot table includes many categories when I add a calculated item as long as it is in the same category it is working as fine.
- But when I add a calculated item that includes items from different categories in the same field/column, it is not working it is adding more than one item according to your equations and calculating each category as a stand-alone.
I've uploaded a video explaining my point here:
Hi,
Thanks for sharing your file and video explanation.
Each row label in the PivotTable acts as a filter. When you have the main category field in the PivotTable AND the sub-category field, the values are filtered by both of these fields. The Owners Equity main category doesn't contain current liabilities and current assets, therefore it will not include those item's values in the sum.
If you remove the Main Category field from the PivotTable it is no longer filtered by these items and the values are included in the sum as you expected.
Note: your source data is in the wrong layout. You should not have a separate column for each year. Instead, you should have a single column for the year and another for the value. See this PivotTable video which illustrates the problem.
Hope that helps.
Mynda
Thanks a lot.
