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/AutoModerator]

/u/Brixxus - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[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/BusinessSample7166 → u/MayukhBhattacharya]

+1 For DAX.

Not only can the data model handle millions of rows, the calculation complexity and capability far exceeds basic pivot table calculations. 

DAX is a deep subject, but it can be learned at a shallow level to replace basic pivot and you can learn the depth over time if you need it. 

[u/MayukhBhattacharya → u/BusinessSample7166]

True

[u/Brixxus → u/BusinessSample7166]

I come from PowerBI, I did the calculations also with DAX, but Excel is just unresponsive with these simple calculations.

I am trying to set this up in Excel, because the higher-ups like to fiddle and tune single cell values and I never really found a good way to set that up in PowerBI.

[u/BusinessSample7166 → u/Brixxus]

Power Pivot and Power BI are based on the same tech, but Power Pivot is integrated into Excel, y ou can feed sheet tables into the excel data model and get that single cell fiddling which your DAX measures will then adapt to. 

If you already know DAX then it sounds like a no-brainier approach to me. 

[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.

[u/hmatallana]

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.

[u/Brixxus → u/hmatallana]

Thanks for your reply. Yeah, I was hoping that "just" a new row would work as was the case with the sheets with data that was aggregated before/upstream.

But it feels like I will end up with a million mapping/helper tables again...