Disaggregate Data in Pivot Tables

[antinora]

Greetings:

I'm a frequent user of basic Pivot table functions. With the attached data image, I'm trying to disaggregate TOTAL "Replace" items cost by floor (either 4 or 6).

I can get a count of all rows in Floor column. Been searching high and low for a way to disggregate by floor.

My client needs

• Total items in list (easy to do)
• Total cost for "replace" items
• Then total items for each floor and cost for each floor

Unsure if Pivot table can do this.

I've been using SUMIF and SUMIFS functions to create the data in chart format.

Just hoping to be more efficient with Pivot table. Also, less possibility for formula errors on my part.

Thanks for considering helping me.

 

[awoohaw]

can you post your data using the xl2bb add in please? The link is below. That way we do not have recreate your data.
Please.

 

[awoohaw]

somthing like this, you can filter disposition to show only Replace:

WorkBook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Floor	Disposition	Cost			Floor	Disposition	Count of Floor	Sum of Cost
2	4th	Replace	400			4th	Repair	2	2000
3	4th	Repair	1300				Replace	2	1300
4	4th	Replace	900			4th Total		4	3300
5	4th	Repair	700			6th	Repair	4	2700
6	6th	Replace	700				Replace	3	2300
7	6th	Repair	700			6th Total		7	5000
8	6th	Replace	600			Grand Total		11	8300
9	6th	Repair	700
10	6th	Replace	1000
11	6th	Repair	500
12	6th	Repair	800
13
Sheet3

 

[awoohaw]

somthing like this, you can filter disposition to show only Replace:

WorkBook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Floor	Disposition	Cost			Floor	Disposition	Count of Floor	Sum of Cost
2	4th	Replace	400			4th	Repair	2	2000
3	4th	Repair	1300				Replace	2	1300
4	4th	Replace	900			4th Total		4	3300
5	4th	Repair	700			6th	Repair	4	2700
6	6th	Replace	700				Replace	3	2300
7	6th	Repair	700			6th Total		7	5000
8	6th	Replace	600			Grand Total		11	8300
9	6th	Repair	700
10	6th	Replace	1000
11	6th	Repair	500
12	6th	Repair	800
13
Sheet3

Actually, this will not be a snapshot, you'll have to toggle the filter on and off to see the totals. PIVOT Table MDX may be a solution.

 

[awoohaw]

Here is the Mr. Excel video I watched on MDX. I think you'll be able to do what you want with this.

Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX - 2547

If you need to hide some rows in an Excel pivot table, you can Create a Set Based on Row Items. This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model. Once you create a set, it is a bit strange to remove rows. And if you add data later, you...

www.mrexcel.com