5 Replies Latest reply on Oct 17, 2018 5:53 PM by tanvi khanna

    weighted data at different level

    tanvi khanna

      Hi,

       

      I am having data at business groups/units level (geography hierarchy).

      The weighted NPS is calculated at business units or business group levels as in the workbook attached

       

      Ques: I need to continue to show NPS at business units level as it is currently in tab"NPS bar", however when the visual is switched to business group level in tab "NPS bar business group", the calculation should be same as the avg(%NPS) of business units in that business group tab: "table business grp"

       

      the Total here is the average of all units (e,h) in Asia:

      need this in bar chart which is currently the automatic table calculation from raw data - i want the numbers to be the avg like in the table above:

      eg: ASIA the green(promoters) should be 10.8 instead of 15.82 as it the average.

      Likewise for all regions, the global NPS = (0.6 * %NPS for CA )+ (0.2 * %NPS for US )+ (0.2 * %NPS for Asia) -when I remove the business groups view, tab: global

       

      workbook attached

       

       

      any urgent help is highly appreciated!

      Thanks

        • 1. Re: weighted data at different level
          ShivaRam Chennapragada

          Can you explain the logic behind TOTALs in sheet 'table business grp'?

          • 2. Re: weighted data at different level
            arvindgarg

            I have read your question multiple times. But by the time, i reach at the end. I get confuse, what are you looking for.

             

            I hope, it is for me only.

            • 3. Re: weighted data at different level
              Joe Oppelt

              I deleted the thread that you used to point to this one.  You have some attention here now.

              • 4. Re: weighted data at different level
                tanvi khanna

                Hi Thanks for the comments. I will explain in detail:

                 

                We want to see %NPS (an aggregated measure) at 3 levels of geography - Business units(level 0), Business groups(level 1) and Global level(level 2). The data is a flat file with each record having columns for all measures including business unit or group it belongs to.

                 

                Challenge: The %NPS calculated for each business unit is correct, but for %NPS for each business group (a level above unit) should be the avg(%NPS of all business units in that group) that is seen in the TOTAL(in the table):

                I have aggregated as totals by average in this case. [analysis->totals->total all using->average] I need to create visuals that show %NPS at business group level (view should not have business unit)- a bar chart in my tableau workbook.

                 

                For instance, %NPS for CA = avg (%NPS of e,h) but as soon as move the business units out of view the %NPS again recalculates based on the raw file and is no more the avg of the business units in them.

                I tried exclude function, but exclude is not running as %NPS is already an aggregated field.

                 

                In addition to this situation, the global (level above groups) should be a weighted number by groups as

                (0.6 * %NPS for CA )+ (0.2 * %NPS for US )+ (0.2 * %NPS for Asia)

                Please let me know if more clarification is needed.

                Thank you,

                Tanvi

                • 5. Re: weighted data at different level
                  tanvi khanna

                  Hi @ShivaRam

                   

                  Thanks for your reply.

                   

                  The TOTAL is the %NPS at the business group level (region level)

                  Here I have performed total using average as I need the %NPS of a region to be the average of the units it has.

                  However, the challenge I face is that in visuals I need to show only business group level, but as soon as I remove the business units from the view the total using average switches to auto calculations and the number is different.

                   

                  For instance, in my data for Asia %promoters = 15.82 as auto calculated but average of units gives 10.8

                  I want to use this 10.8 as the correct number to populate visuals and not show units as a part of the visuals.

                  I tried exclude function but since %NPS is an aggregated field, I am not able to proceed.

                   

                  In addition to this situation, the global (level above groups) should be a weighted number by groups as

                  (0.6 * %NPS for CA )+ (0.2 * %NPS for US )+ (0.2 * %NPS for Asia)