In other words (hopefully clearer!) is it possible to change a crosstab input to a vertical table in tableau itself, or will this need to be done at the datasource?
1 of 1 people found this helpful
I would try to get the three columns into one, either at the source, or using custom SQL. Then it should be fairly straightforward.
I used Alex's suggestion of Custom SQL in Tableau and a table calculation to find the mode. The Custom SQL was to UNION the original table together twice more, to create a combined R123 column.
From there, I did the following:
- Create a calculated field called Mode of R123 that just has the value [R123] and convert that to a Dimension. This is to have appropriate header text, and/or to allow using ATTR(R123) as a field in results.
- Create another calculated field called Mode Filter for R123 with the following calculation:
IF COUNT([R123]) = WINDOW_MAX(COUNT([R123])) THEN 1 ELSE 0 END
- Drag Mode of R123 onto the Rows Shelf.
- Drag Mode Filter for R123 onto the Filter Shelf. Set it to filter to 1, and set the Compute using to Mode of R123.
- Right-click on the City dimension and turn on Quick Filter.
You can see all this in the attached workbook.
One note - I saw that R1, etc. all used whole numbers. If you are using decimals, then you'll probably need to change the Mode of R123 calc to truncate or round in order to have an accurate bin size.
mode filter.twbx.zip 13.1 KB