Excel Forum

Adding customised summary rows after a group in a pivot

Ahmad Algadeeb

Active Member

Members

Forum Posts: 4

Member Since:
May 29, 2022

Offline

May 29, 2022 - 2:39 pm

I have been using pivot tables for a bit and they are time saving at times.

I have a database of projects their budgets and costs. I know that I can create custom subtotals and custom calculated columns, but was wondering if i could add a line underneath a subtotal to do a calculation e.g. i have one budget for the whole project but several categories of expenses. So I need to show the breakdown of expenses and at the end sum of it and then a new line showing the value of surplus or deficit. Another thing I just noticed, if I added it to a data model to create a measure and check the variance, I am not able to choose custom subtotals I would prefer to do it without VBA, but if it is a must, why not

I hope I can find a solution as otherwise will have to do it manual for above 50 tables

Attachments

pivot_question.jpeg (39 KB)

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 3, 2022 - 7:35 pm

Hi Ahmad,

You should not add custom rows in a pivot table, this is not just unusual but also not recommended, even if there are some workarounds.

Instead, you should have a table with those calculations, and add this table with a relationship to the parent table with expenses.

Ahmad Algadeeb

Active Member

Members

Forum Posts: 4

Member Since:
May 29, 2022

Offline

June 3, 2022 - 11:33 pm

Hi Catalin

thank you for your feedback. Just to make sure we are on the same page, what i meant by adding a row was a summary stat under the subtotal, do I make sense?

if there is a way, would appreciate altering the file i attached or even create a new one

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 6, 2022 - 2:24 pm

Hi Ahmad,

Custom rows are possible, you can take a look at these posts:

https://storybi.com/2022/04/09.....subtotals/

https://community.powerbi.com/.....d-p/200540

Can you create please a sample of the desired result, with normal excel formulas in a range, instead of pivot tables, so I can see exactly what you're after, including your formulas?

Thank you

Ahmad Algadeeb

Active Member

Members

Forum Posts: 4

Member Since:
May 29, 2022

Offline

June 7, 2022 - 2:57 pm

thank you for this Catalin. I will have a deeplook at the links you attached. They look promising and need a bit of work

attached is a sample below the row data

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 8, 2022 - 3:46 pm

Hi Ahmad,

Building a data model needs a properly organized data.

For example, the budget seems to pe Per Project, not per expense, so repeating the budget at each row in the fact table is not right. It makes more sense to have a dimension table for budget per project.

You can use measures that are calculated only in the totals and subtotals areas:

Surplus/Deficit:=IF(COUNTROWS(VALUES(Table1[Expense]))>1,[Sum of Budget 2]-[Sum of Expense Value 2],BLANK())

Looks much cleaner than another custom row, IMHO: