Different Filters for Different Columns

[eliwaite]

I would like to know if there is anyway to use the filters of a pivot table and have them apply to specific columns. I am using PowerPivot to combine multiple tables together, which when combined looks like this:

ID	Quarter	Sales	Pipeline	Sales Person
1	Q1	500		James
2	Q1	100		James
3	Q1	350		Jim
4	Q2		100	Jane
5	Q2	250		Jane
6	Q2		400	Jim
7	Q2	300		James
8	Q3		600	Jane
9	Q3		150	Jim

<tbody>
</tbody>

I would like my output to look like this:

Sales Person	Q1 Sales	Q2 Sales	Q2 Pipe	Q3 Pipe
James	600	300	0	0
Jane	0	250	100	600
Jim	350	0	400	150

<tbody>
</tbody>

Thus Q1 Sales, is the sum of the Sales field filtered for Q1. Q2 Pipe is the sum of the Pipeline field filtered by Q2... and so forth.

I realize I can write formulas (Calculate and Filter) within PowerPivot to get to this same output, but was looking for an easier/simpler way.

Thank for any advice you can provide.

 

[ImkeF]

That's what the Pivot Table in Excel based on your PowerPivot data should do for you:

Drag Sales Person into rows, Quarter into columns & Sales&Pipeline into value area.