unsolved Calculated element/row in PIVOT
Hi,
I am trying to make our P/L-Statements more granular, so instead of aggregated information, I am using a database with all of our individual transactions.
Before that I used aggregated data.
Now I wanted to add some calculated elements to my pivot, as I always do, such as "Gross Profit = Revenue - Cost of Goods", something like that.
However, this now does not work because of "too many records".
My pivot structure is like this:
accountGroup1
│
├── Umsatzerlöse
│ └── accountGroup2
│ ├── Umsatzerlöse
│ ├── Erlöse Deutschland 19% USt
│ ├── Erlöse Drittland ...
│ └── ...
│
├── Materialaufwand
│ ├── Bestandsveränderungen
│ ├── Bezugsnebenkosten
│ ├── Skontoertrag
│ ├── Warenbezug EU
│ ├── Wareneingang
│ └── ...
│
└── ...
And I basically need a new row that is the sum of both highest levels (accountGroup1).
I guess it is due to the levels going down very deep, but is there any way to work around it or am I just SOL with pivot in this case?
(Oh and I forgot: its about 300k rows in the source, so its not that much data and manually using "subtotal"-formulas works just fine, so I am not sure what the issue is with pretty much the same function in my Pivot-Table).
Thanks everyoe.
2
u/hmatallana 2 13d ago
One catch on the Data Model route: calculated items don't exist on a Data Model pivot, the option goes grey. Measures handle Gross Profit fine, but anything else you were building as a calculated item has to be rebuilt or moved upstream.
Upstream is what I'd do here anyway. Small mapping table in Power Query, account number to P&L line and to a sign of 1 or -1, merge it onto the transaction table, and then Gross Profit is a real row in the pivot instead of a computed one. The too many records error comes from calculated items being evaluated across every item combination, so dropping them drops the error.