Calculate and compare selected member to non-selected members

[intapiuser]

 Analytical Need 

You want to display a value for a selected member (i.e. Salesperson) vs the min/max of all non-selected members. This can be achieved using modeling techniques but this solutions describes how to do it purely in the front end.

Solution

Here is a pivot table showing the count of patient visits per doctor month by month. We are filtering the dashboard to a specific doctor, Jermaine.

The question we want to answer is "What value is the best of the rest?" Meaning, based on the selected member, what is the max of the non-selected members in each month? There are three possible outcomes:

1. The selected member is not the max. In this case, the max of the rest = the max of all.
2. The selected member is the max, and the there is no tie for the max. In this case, the max of the rest = the second highest result.
3. The selected member is the max and is tied with at least one other member. In this case, the max of the rest = the max of all.

We use the Filtered Measure certified add-on to accomplish this so make sure to download and enable it!

1. Let's use a pivot to validate our results. Create a pivot that is broken out by month and dimension you plan to filter on and add a value with the calculation, in this case a simple count of patients:
   
   Note the max in each month.
2. Add another value with a formula to calculate the rank within the month:
   
3. Add a third value like the following:
   
   This is checking IF the rank of the row is 1 AND the count of the selected member (using the @ to reference the dashboard filter, per the Filtered Measure plugin) is equal to the max of the count of all members, then set the value to -1. ELSE set the value to the count of the row.
   
   Here are some results:
   
   The selected doctor is Jermaine. In 07/2011, he is tied for the max, so we set one of these values to -1 so that it is no longer the max.  In 08/2011, he is not the max, so we just return the row value. In 08/2012, he is the max, so we set it to -1.
4. Add a final value (the above value was saved as a formula for legibility):
   
   This means get the max of all the above values. After removing the group by on Full Name, here are the results:
   
5. Remove the grouping by member, and change your widget into the desired final form, such as a line chart showing the selected value and the max of the rest:
   

Nice!

[zohebakber]

Hi,

the images are not visible- can you please repost