I haven’t seen this particular trick out there yet, it’s a fun one for the toolbox. When we’ve wanted to mix and match KPIs in a crosstab or text table, we’ve had to resort to multiple worksheets on a dashboard in a layout container, or a multiple axis crosstab. The former can result in various display issues because Tableau imposes certain border sizes, the latter has problems with performance due to the number of separate queries and computations that are necessary.
Tableau version 8 gives us floating dashboard elements with pixel-level precision for element location, and that gives us a third way to build text tables with KPIs.
The text table is a standard Measure Names/Measure Values table, with the spacing made a little wider, and put onto the dashboard as a floating element. The KPI is set up as another floating worksheet with hidden headers and sized to go in exactly the right place. Some other notes on this technique:
- In Tableau Desktop version 8.0.1, I had to actually place the KPI worksheet 2 pixels higher for it to be vertically aligned in Tableau Public, here’s what it looks like in Desktop:
- Also, in the screenshot and the workbook above you may detect one pixel of white space between the right edge of the KPI and the background worksheet. It seems like there’s no way to get rid of all the whitespace, though we can come close. I’ve sent this and the other issue on in to Tableau support.
- Tableau doesn’t default to assigning shading to Shape, Circle, or Square Mark Types, I had to manually set that up.
If it weren’t for wanting the continuous borders and shading, those latter two issues wouldn’t even be noticeable, and generally my workbooks are for Desktop or Server/Public consumption (but not both), so the first issue, though irritating, at least has a workaround. There are definitely places where I can use this in some upcoming work!
Hi
Thanks for a cool workaround – however it creates it own usability issues. Now if you expand the category og sort on one of the measures then the KPI’s are not following. Is there any way to disable the expand and sort functionality or have the KPI worksheet follow the behaviour of the ‘parent worksheet’
//Peter
Good points, Peter. The hierarchy drill-down and one-click sort features are applied to one particular worksheet, there’s no way I know of for that change to be applied to another worksheet. We can disable hierarchy drill-down by using a dimension that isn’t part of a hierarchy, for example by using a copy of Category, or for dates by creating a string field. For text marks, we can effectively disable one-click sort by using a string for the label instead of the measure, but then you’re limited to one measure column and we can’t use Measure Names/Measure Values to build the table. Otherwise, I don’t know of a way to disable one-click sort.
There are a couple of workarounds using parameters, though you’d have to train your users to not use the hierarchies (or get rid of them by the technique I mentioned) and one-click sorts.
– For hierarchies, you can create a parameter that controls one or more calculated fields that return different levels of the hierarchy.
– For the sort, you can create a parameter that controls a calculated field with a CASE or IF/THEN statement which returns different measures, and sort the dimension based on the calculated field.
There’s a way that the hierarchies and one-click sort are “bolted on” to the underlying functionality in Tableau, they aren’t as integrated as we might like them to be.
Jonathan
Nice work.
Do you think it would work to place a calculated field within the table sheet, change marks from text to shapes? That way it wouldn’t be a separate sheet. But I guess you can’t have 2 kinds of marks within the same sheet :/
I built this using a Measure Names/Values table, so if the marks were Shapes then the alignment would look a little weird because Tableau would impose an extra right margin for the space of the Shape. It is possible to use a multiple axis crosstab to get similar results.
Hi,
Can you please tell me how to get the measure alignment as right in above kind of dual axis cross tab report. I tried going to format and in alignment as right. But it’s not working for me.
Please reply.
Thanks
This wasn’t laid out with a dual axis, it was laid out with a floating element carefully placed over the original. If that’s not enough detail for you please post a question on the Tableau forums http://community.tableau.com and put a link back here and I’ll check it out when I can.