1 Reply Latest reply on Jan 30, 2014 2:51 AM by Jim Wahl

    Measures distribution

    malou.puyod

      I have dimension TYPE and its corresponding measures/data.  In order to get the KPI of each Type the formula is Sum(Num) / Sum(Den).  See sample data below.

       

      However,  I need to distribute the data of those Types with multiple types. For example, Numerator of "A / B" row which is 1 will be distributed to Type A and Type B.  Same with its Denominator.

       

      Please see sample screenshots below.

       

       

       

      How can I do this in Tableau?

        • 1. Re: Measures distribution
          Jim Wahl

          Hi Malou,

           

          There are a few different approaches to this, depending on your data source, the amount of data, the view, ...

           

          Below I've described how to do it entirely in Tableau using a self-blend. This only works if the first Type values (A, B, C) in the Type tuple are exhaustive---e.g., you can't have "A / D" type without a "D" or "D / B" or some other Type with D in the first position.

           

          This approach can be confusing. If you can modify the data source, it might be better to create a new data source for this KPI calculation that has only one Type / row. (You could probably do this with Custom SQL, but the format will depend on your database.)

           

          But, here's the result using a self-blend.

          2014-01-30_12-16-11.png

           

          First, parse the Type into two parts,

          Type 1 =

          IF    FIND([Type], " / ") > 0 
          THEN  TRIM(LEFT([Type], FIND([Type], " / ")))
          ELSE  TRIM([Type])
          END
          

           

          Type 2 =

          IF    FIND([Type], " / ") > 0 
          THEN  TRIM(RIGHT([Type], LEN([Type]) - FIND([Type], " / ") - 2))
          ELSE  NULL
          END
          

           

          Next, duplicate the data source by right-clicking the data source > Duplicate. (This creates a second connection.) To prevent some confusion, I edited the name to be "Dupe for Type Combination".

           

          Click the duplicate data source and rename Type 1 to Type 2 and Type 2 to Type 1. I'll explain below.

           

          Finally go back to the original data source and create new calculated fields for the combined values

          Num Combined =

          SUM([Num]) + SUM([Dupe for Type Combination].[Num])

           

          Dem Combined =

          SUM([Den]) + SUM([Dupe for Type Combination].[Den])

           

          Now drag Type 1 to the Rows shelf. Double-click the new fields to add the Measure Names / Measure Values fields.

           

          If you click on the secondary data source, you'll see an orange link next to Type 1, showing that Tableau has linked the data sources on this value. But because we swapped the Type 1 and Type 2 names in the duplicate data source, we're getting the sum of all Type 1s in the primary and the sum of all Type 2s in the secondary.

           

          And now you can create the KPI field as just Num Combined / Dem Combined.

           

          Again, the major issue with this approach is that it will only include Types with a value in the first position.

           

          Jim