12 Replies Latest reply on Dec 12, 2016 4:41 AM by Michael Hesser

# Educational Brain Teaser: Twisted KPI Table

Hello my fellow tableau geeks,

I'd like to share a puzzle some of you might find curious. Using the Superstore sample data can you create a view like this in a single sheet?

Requirements:

• single sheet
• 7 measures displayed and formatted as depicted
• totals per measure
• shapes only displayed in the right column; various shapes for selected measures (arrows, ticks, other); color is uniform: red for negative delta and green for positive
• the two columns headers for actual value and delta (year over year)
• the year is displayed in the primary columns header as per filter selection:

the year 2013 may look more interesting:

Cheers,

Łukasz

• ###### 1. Re: Educational Brain Teaser: Twisted KPI Table

anyone giving this a try?   I see some conditional formatting tricks but the use of the totals for each measure is not easy for me to replicate.

• ###### 2. Re: Educational Brain Teaser: Twisted KPI Table

I know I can make this happen with a tableau feature (won't say in case others are curious) and a painful string calc to get the formatting right . Do you have some extra formatting trick up your sleeve?

Dan

• ###### 3. Re: Educational Brain Teaser: Twisted KPI Table

Thanks for your comments. I am curious to see what tableau feature you have in mind

In fact totals are not that tricky at all - most of them at least .

As to the formatting - it is not a calculated string but numeric values with in built formatting - I used the generated [Measure Values] pill in labels:

Oh, and let me precise one thing - there is one measure I defined for this exercise: [# Unique Customers] and it is the count of distinct [Customer Name].

Łukasz

• ###### 4. Re: Educational Brain Teaser: Twisted KPI Table

Did you need to make calculations for each Measure, or were you somehow sneakily able to use the Measure Name/Measure Values to do the work for you?

• ###### 5. Re: Educational Brain Teaser: Twisted KPI Table

Right - I did not want to reveal too much but whatever - one more hint is that what is beneath 'Measure' is not the generated [Measure Names] field. I would not be able to do this if I was to use regular measure names, would I?

Glad it is catching attention; I am planning to reveal all I did over this weekend, unless someone else does it first

• ###### 6. Re: Educational Brain Teaser: Twisted KPI Table

How did you get the subtotal under each section of three? I am beside myself trying to replicate your dashboard but, haven't been able to get the subtotals to work properly. Ideas, thoughts, I'll pay you, etc....

• ###### 7. Re: Educational Brain Teaser: Twisted KPI Table

Hmmm... Did you create a cycle counter {1,2,3,4,5,6,7} ?

Your [CycleTitle] , [Cycle Value], and [Cycle Delta] would be CASE statements based on the counter.

A dual-axis allows for the display of both [Cycle Value] and [Cycle Delta].

[Cycle Delta] is shape controlled by SIGN([Cycle Value]) x Counter, so you have 14 unique values for images. (7 x Pos/Neg). I think you can get the actual value to show up by showing the value (to the right).

Color is just [Cycle Value] with two options.

There seems to be a lot of "heavy lifting" in that approach (and you insinuated you weren't using string formatting). I think I need to give this a rest before I go crazy!

• ###### 8. Re: Educational Brain Teaser: Twisted KPI Table

Hi all,

Lukasz, it was hard.

It would be even harder

if I couldn't been handicapped ;-)

Rate it 4.9 (this is my highest rating so far).

And I suspect you took a different route than mine.

Yours,

Yuri

Proof:

3 of 3 people found this helpful
• ###### 9. Re: Educational Brain Teaser: Twisted KPI Table

Thanks everyone for participating and congrats to Yuri for accomplishing the goal at the last minute

Now it is time to show how I did it.

1. The main challenge is to control shapes for each measure individually based on their values. So I decided to use table calculations for this purpose but had to first somehow split underlying data so that I have a dimension for addressing and partitioning. This is my [split] formula:

where N is an INT parameter here set to 7 as that is how many "measures" I want to display. In result I have the data split roughly in half for each actual partition that will be used (Year, Category). Of course this method requires at least two orders - one even and one odd number - to work. The formula could be something else (e.g. using {MIN([Order ID])} or random value) but this one is good enough.

[Split] field itself yields 2 values so for my 7 measures I will need data densification hence [split (bin)] is created with size of 1. This is my viz foundation:

When dropped onto rows shelf it must show missing values which triggers domain padding from 1 to 7. Since real data is only present in the first and last partition all marks I will display will be based on table calculations.

2. Calculations to be displayed are based on their position computed along padded bins:

* I forgot to mention this before - the first one (discount) is effectively an average

and Δ along year:

and this is what I used for "Measure Names"

Shapes are based on this:

which allows me to select up to 3 different shapes per measure ([index 2] is a nested INDEX() with different addressing):

When I am looking at this now I realize I should have used another calculation for color with just -1, 0, 1 as values but instead I used the same [shape] only with [index 2] addressed so that it never is >1.

Notice that Null color is set to white and that's how I hid the shapes from the left column

3. To show Value and Δ in two columns I decided to use my [split] dimension and simply alias 1 and 7 - now i have to account for both [split] and padded [split (bin)] in context:

4. Rows and columns set up:

where the only continuous pill is RUNNING_MIN(MIN(0)) along [split], [split (bin)] with hidden ticks, fixed range and hidden headers:

I found the range (-1,10) suitable for positioning my shapes and labels

5. Eventually the details shelf with actual [Measure Values] needs to be built. I decided to use unnamed calculations defined in shelf one by one with individual formatting set and dropped on to my values pill:

you may check each pill addressing in the attached workbook

6. Totals - it is simply [split (bin)] subtotal and I only had to customize the [# Unique Customers] value:

where [ #Unique Customers] is simply COUNTD([Customer Name]) - can't remember now why i left that countd in another field - probably wanted to have a standard calc for verification...

Also I later learned that I could use TOTAL() table calc instead of LoD but it would require one more level of nested fields.

7. Year header and filtering - YEAR(Order Date) is filtered in context - in continuous "At Most" mode - in order to be able to use [Max Year] = {MAX(YEAR([Order Date]))} as dimension with one value in context. This trick I have happily stolen from Yuri Fal - natural way of having this header would be to use Year(Order Date) in columns but - because of the "bug" we discussed recently - totals cannot be filtered from cross table (opposite dimension).

Of course LAST()=0 filter is used to filter for the latest Year only.

I hid some grid lines.

I think that's it.

Thanks again,

Łukasz

3 of 3 people found this helpful
• ###### 10. Re: Educational Brain Teaser: Twisted KPI Table

Bravo, Lukasz.

Even if I knew some stuff here,

the main idea of "splitting" datasource

and emulating Measure Names / Values

by densification via Bin / index() combo

is resonated to me the most.

I have to study this a bit more, but essentially

it's a kind of "Excel inside Tableau" --

for those brave enough doing everything Table Calc :-)

I didn't find myself brave enough :-)

So I decided going south -- choosing the road

of cheating with the datasource. So did I.

To get the Measures as Dimensions

I UNIONed the Orders table 7 times

('cause I like the number :-).

To get two columns (yours 'split' ones)

I cross-joined my unioned tables

with the 2-row dataset.

Having this 'dense' datasource in place,

most of Table Calc stuff could be avoided

in favour of regular Aggregates. Bunch of them.

Having everything as Dimensions could allowed

the KPI Shapes to be hand-picked (not calculated).

And there is no need to use here

the trick with Sub-totalling Measure Names --

but I left [Year Max] as a Label on Columns ;-).

That's basically it.

Please find the attached workbook (version 10.1).

Pardon my dear Community friends

who have not yet installed version 10.1.

I've got a buggy behaviour with version

10.0.3 doing extracts in background

when preparing my puffy datasource.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 11. Re: Educational Brain Teaser: Twisted KPI Table

That's cheating

But I did not ascert anything on modifying ds so you are excused

btw you may recall you said that little workaround with the year header was

an idea for a brain teaser. So that was the trigger for this twisted table.

• ###### 12. Re: Educational Brain Teaser: Twisted KPI Table

Awesome!