3 Replies Latest reply on Jul 30, 2018 2:46 AM by Naveen B

    Complicated Dynamic Calculation - Need help from the Community

    Tuyen Nguyen

      Hi,

       

      I'm struggling with a complicated calculation and need help from the community.

       

      To describe my problem, I'll use the following sample dataset in which the measure is headcount.

           

      YearNationalityDivisionTypeLevelHeadcount
      2010USITPTA99
      2010UKFinanceFTA58
      2010AUHRPTC41
      2010JPHRPTB15
      2010CNITFTC5
      2010MXITPTA20
      2010CNFinancePTB32
      2010USHRPTC53
      2010AUITFTB22
      2011CNITFTA90
      2011MXFinanceFTB80
      2011CNHRFTB91
      2011USHRPTC41
      2011AUITFTA95
      2011USFinanceFTC21
      2011UKHRPTB87
      2011AUHRFTB78
      2011JPITFTC92

       

      What I need is a calculated measure called Diversification Ratio (D-Ratio)

       

      The important thing is that Si must always be calculated by Nationality at any level of granularity, and M is the grand total of Headcount and must be dynamic so that it changes base on the level of granularity.

       

      The problem is how to make the calculation of the D-Ratio dynamic so that whenever I apply filters or change the level of granularity, D-Ratio must change accordingly.

       

      For example, I want to have a line graph showing how D-Ratio changes over time, so the D-Ratio need to be calculated by year

      e.g. D-Ratio for 2010 = 0.7275

          

      YearNationalitySMPP*(1-P)D-Ratio
      2010US1523450.440580.24646920.727528
      2010UK583450.1681160.1398530.727528
      2010AU633450.1826090.14926280.727528
      2010JP153450.0434780.04158790.727528
      2010CN373450.1072460.09574460.727528
      2010MX203450.0579710.05461040.727528

       

      Now, if I need to drill it down to Division level, let say a graph showing D-Ratio change over time for HR, so now, the D-Ratio must be calculated by Year and Division

      e.g. D-Ratio for HR in 2010 = 0.6031

           

      Year DivisionNationalitySMPP*(1-P)D-Ratio
      2010HRAU411090.37614680.234660.603148
      2010HRJP151090.13761470.1186770.603148
      2010HRUS531090.48623850.2498110.603148

       

      Again, If I drill down more, let say D-Ratio over time by Division by Level, then again D-Ratio must be recalculated at "Year-Division-Level" level

       

      I don't know how to do it in Tableau at the moment, so really appreciate any help or idea from you guys!

       

      Cheers,

        • 1. Re: Complicated Dynamic Calculation - Need help from the Community
          Naveen B

          Hi Tuyen,

           

          Create calculated fields as mentioned below

           

          Step 1: Create a calculated field for year wise headcount

           

          Step 2: Create a calculated field to compute the sum of the value of headcount per nationality

           

          Step 3: Create a calculated field to compute P

           

          Step 4: Create a calculated field to compute p*(1-P)

           

           

          Step 4: Drag Year to Columns And  P*P(1-p) to rows and change the mak type to line

           

          Step 5: Now you can see year wise D-ratio for 2010 it is 0.72753 and 2011 is 0.80474

           

          Step 6: Drag Division to filter and add it to the context

           

          Step 7: SHow the filter and select HR and value for 2010 is 0.6031

           

           

          Hope this helps and by the way nice concept to work on thanks for it

           

          Kindly mark this answer as helpful and correct so that it will help others

           

          BR,

          NB

          • 2. Re: Complicated Dynamic Calculation - Need help from the Community
            Tuyen Nguyen

            Hi Naveen,

             

            Thanks for your help, but I think this solution cannot solve the problem completely.

             

            It works if I set the Division as Context filter and the graph only has 1 line.

             

            Now if I set the Division as Color, I expect to see 3 different lines for HR, Finance and IT. However, with this, we still only see 1 line as the MDR was calculated by FIXED Year and Nationality only (Actually, there are still 3 lines but they have exactly the same values, so we only see 1 on the graph)

             

            The challenge is how to create the dynamic MDR  calculation so that it changes when we drill-down by using another dimension as Color or Detail, etc. 

            • 3. Re: Complicated Dynamic Calculation - Need help from the Community
              Naveen B

              Hi Tuyen,

               

              Adding dimension to context will increase the granularity

               

              Could you please give me an example or any snapshot of excel graph how you want so that it will be more helpful to decipher the problem

               

              BR,

              NB