Help with Power Pivot setup

[mrhk22]

Hi Experts,

I have a Power Pivot model with 3 simple tables linked only by the "Company" field as indicated below. The scenario is a consulting business and I want to be able to filter by sales person and see the total number of activities by each consultant for the companies that the sales person covers.

I've set up the pivot table fields as indicated. However, the values stay the same when I filter by Sales_Person and all companies are always displayed. I only want to display those companies covered by the sales person. What am I doing wrong? Apologies that I can't attach the file, but hopefully it's clear. Thank you.

COLUMNS: Company
ROWS: Consultant
VALUES: Count of Activity ID
FILTERS: Sales_Person

1. Coverage (*repeated entries for company, since more than 1 sales person may cover a company)

- Company
- Sales_Person

2. Master (single occurence for each company)
- Company
- Tiering Status

3. ServiceDetails
- Company
- Consultant
- Contact Name
- Activity Subject
- Activity ID
- Activity Date

 

[mart37]

Is consultant the same as sales_person?

 

[mrhk22]

Is consultant the same as sales_person?

No, they are two different roles. A sales person sells the consulting service provided by consultants

 

[mart37]

Then it is not possible you want.
since more than 1 sales person may cover a company
How do you know wich sales_person is responsible for a activity by a company?
You need to mention the sales_person in the table: ServiceDetails

 

[mrhk22]

Then it is not possible you want.
since more than 1 sales person may cover a company
How do you know wich sales_person is responsible for a activity by a company?
You need to mention the sales_person in the table: ServiceDetails

I'm not concerned with which sales person is responsible. We aren't selling one-off products/services, it's just ongoing client service. I just wanted to use the pivot table to illustrate to a sales person the consultant activity levels for clients that they have responsibility for. Why doesn't the filter by Sales_Person actually filter anything?

 

[mart37]

There is no relation between sales_person but only between company. So you get all companies.
Power Pivot don't support tables with more - more relations.
In Power Query it is possible to make such a relationship