Hi,
I have conducted and example survey which asked participants to rank their favorite fruit from most preferred = 1, to least preferred = 6. I have the following questions:
- The excel attached is set up in a way that Q1_1 = Apple, Q1_2 = Orange, Q1_3 = Mango, Q1_4 = Pear, Q1_5 = Cherry, Q1_6 = Watermelon. I have used the pivot function in tableau to gather the columns into 1 column (see workbook)
- Then, I have plotted a bar graph which shows count of people by what they have ranked (see workbook)
- My question is, is there a way to pick and display top 3 preferred fruits from this data? I was wondering as it is a ranking data. Any suggestion is welcome and very appreciated
Thank you
Hi @John Lee
Do you want to get the top 3 favorites OVERALL, or the top 3 per person?
We can do both. You have a nice ranking system in place from 1 to 6 (Most Favorite to Least).
Personally, I like reversing this, so our most favorite has the highest number. That's simple to do, it's just:
(7-[Pivot Field Values])
This way most favorites have a high number, least favorites get a low number.
We can then sum up each fruit's "score" using an LOD expression:
Fruit Score
{ FIXED [Pivot Field Names]:SUM(6-[Pivot Field Values])}
This sums up every person's modified rank by fruit.
We can drop this in our columns, and place [Pivot Field Names] in Rows to see a bar graph of each fruit with it's total "Fruit Score".
We don't have our top 3 yet, though...
One of many ways we can do this is create a discrete RANK function.
Fruit Rank
RANK(SUM([Fruit Score]))
You can change this to RANK_UNIQUE if you want to avoid ties.
Drop it in front of your [Pivot Field Names], then drop in Filters.
Edit your filter so only the the top 3 are showing.
Now, if you're paying attention, you will have seen that we could actually combine a lot of these steps into a single equation. I will leave that up to you-- I prefer walking through with "baby steps" 😁
If you want to see the top 3 fruits PER PERSON, place SUM[Pivot Field Values] on Columns, ID on Rows, and ATTR(Pivot Field Values) in Filters. Edit this last one so only values 1-3 are showing.
Hope this helps!
