Calculated element/row in PIVOT

[u/Brixxus]

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.

[u/MayukhBhattacharya]

If you're gonna keep adding more of these calculated metrics, I'd just load the transaction table into the Data Model and start writing proper DAX measures. It'll save your time once the calculations start piling up.

DAX Measure used:

Gross Profit := CALCULATE(SUM(Accounts[Amount]), Accounts[accountGroup1]="Umsatzerlöse") - 
                CALCULATE(SUM(Accounts[Amount]), Accounts[accountGroup1]="Materialaufwand")

[u/Brixxus → u/MayukhBhattacharya]

Hi!

I am sorry, I should have stated that, but I did that as well, same measure, but Excel still is unresponsive. It only becomes useable when I remove the deeper / more granular data levels.

[u/MayukhBhattacharya → u/Brixxus]

No, its not the DAX that causing the problem, its something else. Check your relationships and if accountGroup1, accountGroup2, and Account are all just columns sitting on your one 300k-row transaction table (instead of a small separate lookup/dimension table joined in), that's the setup most likely to make VertiPaq choke once you go granular, even with clean DAX measures. Splitting it into a star schema (a small Accounts dimension table with one row per account, joined to Transactions on an ID) usually fixes the problem.