Calculating a Benchmark

[veno1978]

Hi,

I have a fairly simple data model of three tables a client table (holds unique row for each client), a meeting table (holds multiple rows for each client with meeting details) and a date table (links to meeting date to group into quarter, year etc). I then have a couple of calculated fields to analyse the % of shareholders that voted at the meeting.

This all works pretty well with just one exception, I have a few graphs which users can update using slicers to select a specific client but what I would like to add into the graphs is a benchmark line for the clients sector i.e. so you can compare client results to the overall sector. The sector is stored in the client table.

The problem I have is that when I select a client using the slicer clearly it only returns the clients for the sector rather than the overall sector results due to the way the tables are filtered by the slicer I have tried a few different attempts to get around this but cant find anything that feels right - any suggestions please?

Thanks

 

[Sinon]

How do you store the sector data? Can you add the sector information similar to how you add a client in the client table (basically, treat the sector as a "client" of sorts)? That way you can use the slicer and choose the client and the sector.

 

[scottsen]

Hard to say without more specific example, but in general you need to "clear the filter on the value in the slicer" in a measure. eg: =CALCULATE([MyValue], ALL(Clients[ClientName]))

 

[veno1978]

Thanks but I am not quite sure how I would get that to work with my current set up, I have a client table which has the following columns, as an example:

ID Client Sector
1 Hats & Shoes Consumer Goods

Then the meetings table which in effect is (link between the two is on ID / Client ID):

Client ID Meeting Date Shareholders Votes
1 10/08/2015 10,000 5,000

The calculated field then calculates a voting % by looking at "Votes" / "Shareholders", as I have a number of years history I then plot this out by year.

I have tried creating an independant sector table linked to eh meeting data (i added the sector data into the table to test it) but this still has the same issue of just returning the data of the client selected in the slicer.

I am wondering whether I require the client table, my rationale for including it was to reduce repeating the capture of client data and being able to link data when the client name changes.

 

[veno1978]

Thanks Scott - I havent tested fully but that seems to do the trick