Hi
I have a Pivot Table (created from an Excel Data Model) that shows the customer, product range (3 ranges) and total spend per product range.
What I want is to return the highest spending product range for each customer. I have tried using the 'Top Filter' in the Pivot Table but all that happens is the highest value is shown but the remaining 2 product range labels are still visible with just no value.
This is what I have using Top 1 per parent row...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Range[/TD]
[TD]Total Spend[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Table[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chairs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Accessories[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Chairs[/TD]
[TD]4,500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Accessories[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Accessories[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chairs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tables[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is what I want....
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Range[/TD]
[TD]Total Spend[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Table[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Chairs[/TD]
[TD]4.500[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Accessories[/TD]
[TD]2,000[/TD]
[/TR]
</tbody>[/TABLE]
Is this possible?
Any help would be great.
I have a Pivot Table (created from an Excel Data Model) that shows the customer, product range (3 ranges) and total spend per product range.
What I want is to return the highest spending product range for each customer. I have tried using the 'Top Filter' in the Pivot Table but all that happens is the highest value is shown but the remaining 2 product range labels are still visible with just no value.
This is what I have using Top 1 per parent row...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Range[/TD]
[TD]Total Spend[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Table[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chairs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Accessories[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Chairs[/TD]
[TD]4,500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Accessories[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Accessories[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chairs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tables[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is what I want....
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product Range[/TD]
[TD]Total Spend[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Table[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Chairs[/TD]
[TD]4.500[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]Accessories[/TD]
[TD]2,000[/TD]
[/TR]
</tbody>[/TABLE]
Is this possible?
Any help would be great.