11 Replies Latest reply on Nov 25, 2016 3:07 AM by Dane Poboka

# Calculating Top/Bottom Measures?

Hi there,

I'm a new user of Tableau and have encountered a problem I'm finding difficult to solve....I'm hoping someone here may be able to assist please?

I have created a bar chart for 10 discrete competency measures (separate columns in my data file) that show employee performance averages across various departments (aka "functions") within the organization (see screenshot).

I would like to filter this chart to show only the top and bottom 3 competency measures, which will change depending on which Function filter (H1 - H9, All) is selected.

For some reason, I cannot seem to crack this issue even though I'm sure it's a straight forward fix!   Any advice would be greatly appreciated please

Kind regards,

Dane.

• ###### 1. Re: Calculating Top/Bottom Measures?

If possible, could you attach packaged workbook?

Thanks,

Shin

• ###### 2. Re: Calculating Top/Bottom Measures?

Hi Shin,

Thanks for the reply - I've attached the workbook now

Cheers,

Dane.

• ###### 3. Re: Calculating Top/Bottom Measures?

Thank you, appreciated.

Will take a look.

Shin

• ###### 4. Re: Calculating Top/Bottom Measures?

Dane,

I recommend that you change the data structure first to handle these type of table which has many columns as 1-5 type of score.

Edit datasource and pivot data.

[Rank ASC]

if rank_unique(avg([Pivot Field Values]),'asc') <=3 then 1 else 0 end

[Rank DESC]

if rank_unique(avg([Pivot Field Values]),'desc') <=3 then 1 else 0 end

[Filter Top/Bot X]

if [Rank ASC]+[Rank DESC]>0 then "Show" else "Hide" end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 5. Re: Calculating Top/Bottom Measures?

Hi Shin,

This is brilliant - thank you so much! It wasn't as straight forward as I had expected, which is great because I've learned something new and now also have a working solution

Thank you again, really appreciate your help with this.

Kind regards,

Dane

• ###### 6. Re: Calculating Top/Bottom Measures?

No problem.

That's the right way to use the community.

Thanks,

Shin

• ###### 7. Re: Calculating Top/Bottom Measures?

Hi Shin,

I have a follow-up question regarding the above, if OK please?

I have managed to successfully implement your solution and am now able to display top/bottom 3 competency measures using the pivot function (thanks again). However, when I now use the same connection/sheet for further analyses, I've noticed the calculations are incorrect. For example, when the data was previously structured using one row to represent a participant record, I could cacluate how many participants I had from specifc organisational functions by counting the number of records (rows) against organisation function. I'm now getting inflated numbers due to the multiple records as a result of the pivot

I'm guessing I've got my data structured incorrectly to be able to still use the pivoted data and also the original format for other calculations. Ideally, I'll be using a live connection so I can continually add new records to the dataset in the future.

Do you know the easiest way to achieve this please?

Many thanks,

Dane.

1 of 1 people found this helpful
• ###### 8. Re: Calculating Top/Bottom Measures?

If you count participant ID, use "countd" instead of "count".

Thanks,

Shin

• ###### 9. Re: Calculating Top/Bottom Measures?

Hello Dan,

You can achieve the above requiremnt by multiple ways.

Regards,

Sudhakar Reddy

• ###### 10. Re: Calculating Top/Bottom Measures?

That's the trick! Thank you Shin.

Cheers,

Dane.

• ###### 11. Re: Calculating Top/Bottom Measures?

Thank you for your response Sudhakar

Kind regards,

Dane