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

    Educational Brain Teaser: Twisted KPI Table

    Łukasz Majewski

      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
          Patrick A Van Der Hyde

          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
            Dan Huff

            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
              Łukasz Majewski

              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
                Michael Hesser

                Another clue, please

                 

                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
                  Łukasz Majewski

                  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
                    Keith Conroy

                    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
                      Michael Hesser

                      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
                        Yuriy Fal

                        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.

                        So waiting patiently for your reply (showing hands).

                         

                        Yours,

                        Yuri

                         

                        Proof:

                        EBT_LM_Twisted_KPI_Table_YF.png

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

                          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
                            Yuriy Fal

                            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
                              Łukasz Majewski

                              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.