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

Calculating Top/Bottom Measures?

Dane Poboka Nov 16, 2016 7:53 AM

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.

Capture.PNG 122.8 KB

1. Re: Calculating Top/Bottom Measures?

Shinichiro Murakami Nov 16, 2016 8:00 AM (in response to Dane Poboka)

If possible, could you attach packaged workbook?

Thanks,
Shin

Packaged workbooks and flows: when, why, how
Like Show 0 Likes(0)

Actions
2. Re: Calculating Top/Bottom Measures?

Dane Poboka Nov 16, 2016 8:22 AM (in response to Shinichiro Murakami)
Hi Shin,

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

Cheers,
Dane.

SE_DC2016_forum.twbx 47.8 KB
Like Show 0 Likes(0)

Actions
3. Re: Calculating Top/Bottom Measures?

Shinichiro Murakami Nov 16, 2016 8:36 AM (in response to Dane Poboka)

Thank you, appreciated.
Will take a look.

Shin
Like Show 0 Likes(0)

Actions
4. Re: Calculating Top/Bottom Measures?

Shinichiro Murakami Nov 16, 2016 9:18 AM (in response to Dane Poboka)
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

SE_DC2016_forum_SM_10.1.twbx 90.8 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
5. Re: Calculating Top/Bottom Measures?

Dane Poboka Nov 16, 2016 9:27 AM (in response to Shinichiro Murakami)

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
Like Show 0 Likes(0)

Actions
6. Re: Calculating Top/Bottom Measures?

Shinichiro Murakami Nov 16, 2016 10:49 AM (in response to Dane Poboka)

No problem.
That's the right way to use the community.

Thanks,
Shin
Like Show 0 Likes(0)

Actions
7. Re: Calculating Top/Bottom Measures?

Dane Poboka Nov 24, 2016 7:54 AM (in response to Shinichiro Murakami)

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
Like Show 0 Likes(0)

Actions
8. Re: Calculating Top/Bottom Measures?

Shinichiro Murakami Nov 24, 2016 4:10 PM (in response to Dane Poboka)

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

Thanks,
Shin

Getting the Total for Count Distinct | Tableau Software
Like Show 1 Likes(1)

Actions
9. Re: Calculating Top/Bottom Measures?

sudhakar reddy Nov 24, 2016 10:09 PM (in response to Dane Poboka)

Hello Dan,

You can achieve the above requiremnt by multiple ways.

Below are some helpfu links that might be helpful for you.

Re: How to create Top 5 and Bottom 5 values with respect to a Measure in Bar graph in same worksheet

Re: Using RANK to get top 10 and bottom 10 in one calculated field?

Regards,
Sudhakar Reddy
Like Show 0 Likes(0)

Actions
10. Re: Calculating Top/Bottom Measures?

Dane Poboka Nov 25, 2016 3:07 AM (in response to Shinichiro Murakami)

That's the trick! Thank you Shin.

Cheers,
Dane.
Like Show 0 Likes(0)

Actions
11. Re: Calculating Top/Bottom Measures?

Dane Poboka Nov 25, 2016 3:07 AM (in response to sudhakar reddy)

Thank you for your response Sudhakar

Kind regards,
Dane
Like Show 0 Likes(0)

Actions