10 Replies Latest reply: Oct 27, 2018 3:35 AM by Jaspreet Mehra RSS

    Not able to calculate Average distribution

    Jaspreet Mehra

      Hello Community,

      I am try to show the distribution of Connections. Below is the example and I need percentage of distribution.

       

      Connection_TypeDistributionNeed This
      A-92-6%
      B-30-2%
      C906%
      D131%
      E-111-7%
      F49432%
      G28618%
      H40126%
      I50032%

       

      To calculate the distribution. I am using below set analysis that i can't change.

      sum(aggr(SUM(DISTINCT Amount),Emp_id))/ COUNT(DISTINCT Emp_id)  

       

      Thanks in advance.

        • Re: Not able to calculate Average distribution
          Jordy Wegman

          Hi Jaspreet,

           

          Can you give us the values for the Amount and Emp_id?

          Your current formula doesn't contain any set analysis btw.

           

          Regards,

          Jordy

            • Re: Not able to calculate Average distribution
              Jaspreet Mehra

              Hello,

               

              Please find below sample raw data.

              Sorry unable to PFA raw data

                  

              CONNECTION TYPEEmp IdAmount
              E2801811230
              E2803451230
              E280725342-68.4
              E280725342-68.4
              E280725342-68.4
              C1.60473E+32-1008
              C1.60473E+32-1008
              G1.60473E+32-1008
              C1.87063E+32-1588
              A1.51E+46100
              C1.00206E+35-360
              C1.00221E+182320
              F1.00221E+182320
              C1.00221E+182320
              C1.00295E+35-1050
              F1.00339E+350.06
              C1.00388E+35-799
              C1.004E+35-835.68
              C1.004E+35-835.68
              C1.004E+35-835.68
              C1.004E+35-835.68
              F1.00551E+350
              A6.23E+46100
              G1.00639E+350
              F1.0064E+350
              A8.63E+46100
              G8.63E+46100
              F8.63E+46100
              UNKNOWN1.01022E+210
              G1.01037E+311520
              G1.01078E+35356.47
              G1.01078E+31-66.62
              F1.0108E+350
              A1.09E+47100
              F1.09E+47100
              F1.09E+47100
              A1.09E+47100
              D1.01345E+210
              D1.01345E+210
              F1.01413E+350
              D1.01652E+210
              D1.01652E+210
              D1.01652E+210
              UNKNOWN1.01652E+210
              D1.01652E+210
              D1.01841E+210
              D1.01842E+210
              D1.01844E+210
              D1.01846E+190
              D1.01851E+210
              UNKNOWN1.01851E+21-1324.69
              UNKNOWN1.01859E+221872.83
              UNKNOWN1.01859E+22832.27
              D1.0186E+220
              UNKNOWN1.0186E+22265.11
              UNKNOWN1.0186E+22265.11
              D1.0186E+220
              UNKNOWN1.0186E+22915.03
              D1.01862E+220
              A010300268DN1345-134.1
              A010400499DN1345-159
              A010400499DS13410-160
              A010400982DS1342-160
              A010401468DN13450
              A010544DS1342-32
              A010700500DS13450
              A010700504DS13440
              A010700733DS3344-128
              A010800725DS1344-51.2
              A010800814DS13440
              A010801811DS1342-128
              A010801927DS1344-40
              A010802300DS2343-128
              F1.08801E+110
              A011827DS1343-160
              A011001923DS13470
              A11040142345-42
              A011200224DS1349-160
              A011201065DN1343-119.2
              A011201065DS13412-89.6
              A020200839DN1342-119.2
              A020250949DN1345-110
              G20380476349-1313.82
              A020400319DS1348-115
              A020401548DS1346-99
              B20470661345-120
              A020501729DN1343-119.2
              A020501729DS1344-119.2
              A020601393DS11230
              A020602323DN1342-350
              A020702131DS1342-16
              B210303123430
              B306703383470
              G407013803470
              G407013813480
              B50200663341-11.68
              G504011613440
              G50573348115
              B506015341-50
              H508029773420
              G511026493430
              G511026523430
              B51202387344-199
              H602032673430
              H602049413490
              H604034803430
              B60503883343-110
              H609029713410
              B704023053410
              B706033593440
              B70702430343-540
              H70800566343-1470
              B711031433410
              B100466344-140
              B1882349-25
              B1883342-25
              B3556344-25
              B3836344-315
              B4760346-119
              B2818342-25
              B2865342-25
              B2927344-25
              B2928344-25
              B2929346-110
              B2938346-25
              H90402220348-214
              H9.04022E+11-95
              H90901382342-110
              H91101822341-1100
              H1.00402E+110
              H1.005E+110
              H1.10303E+11-1851.19
              H111138341-135
              H1.20303E+11-526
              H1.20602E+110
              D2.06715E+160
              D2.07846E+160
              D2.08513E+190
              D2.09413E+190
              UNKNOWN2.00932E+200
              UNKNOWN2.00934E+200
              UNKNOWN2.00934E+200
              UNKNOWN2.00934E+200
              UNKNOWN2.00934E+200
              UNKNOWN2.00937E+220
              UNKNOWN2.00939E+222276.52
              UNKNOWN2.00939E+222276.52
              H1818841800
              H1818841800
              H4201104760
              F4201145550
              B1149240
              F1149240
              B1205710
              B1205710
              G4201263652
              G4201266149.92
              H4201266149.92
              C4201299649
              C4201299649
              E42013110
              E42013110
              E42013151750
              E42013151750
            • Re: Not able to calculate Average distribution
              Anil Samineni

              Try this?

               

              Num((Sum(aggr(SUM(DISTINCT Amount),Emp_id))/ COUNT(DISTINCT Emp_id)) /(sum(aggr(SUM(TOTAL Amount),Emp_id))/ COUNT(TOTAL Emp_id)) ,'#,##0%')

                • Re: Not able to calculate Average distribution
                  Jaspreet Mehra

                  Sorry Anil, its not giving want i am looking for. its giving me sum of individual connection type inside of sum of overall.

                  Please find below sample raw data for testing

                     

                  CONNECTION TYPEEmp IdAmount
                  E2801811230
                  E2803451230
                  E280725342-68.4
                  E280725342-68.4
                  E280725342-68.4
                  C1.60473E+32-1008
                  C1.60473E+32-1008
                  G1.60473E+32-1008
                  C1.87063E+32-1588
                  A1.51E+46100
                  C1.00206E+35-360
                  C1.00221E+182320
                  F1.00221E+182320
                  C1.00221E+182320
                  C1.00295E+35-1050
                  F1.00339E+350.06
                  C1.00388E+35-799
                  C1.004E+35-835.68
                  C1.004E+35-835.68
                  C1.004E+35-835.68
                  C1.004E+35-835.68
                  F1.00551E+350
                  A6.23E+46100
                  G1.00639E+350
                  F1.0064E+350
                  A8.63E+46100
                  G8.63E+46100
                  F8.63E+46100
                  UNKNOWN1.01022E+210
                  G1.01037E+311520
                  G1.01078E+35356.47
                  G1.01078E+31-66.62
                  F1.0108E+350
                  A1.09E+47100
                  F1.09E+47100
                  F1.09E+47100
                  A1.09E+47100
                  D1.01345E+210
                  D1.01345E+210
                  F1.01413E+350
                  D1.01652E+210
                  D1.01652E+210
                  D1.01652E+210
                  UNKNOWN1.01652E+210
                  D1.01652E+210
                  D1.01841E+210
                  D1.01842E+210
                  D1.01844E+210
                  D1.01846E+190
                  D1.01851E+210
                  UNKNOWN1.01851E+21-1324.69
                  UNKNOWN1.01859E+221872.83
                  UNKNOWN1.01859E+22832.27
                  D1.0186E+220
                  UNKNOWN1.0186E+22265.11
                  UNKNOWN1.0186E+22265.11
                  D1.0186E+220
                  UNKNOWN1.0186E+22915.03
                  D1.01862E+220
                  A010300268DN1345-134.1
                  A010400499DN1345-159
                  A010400499DS13410-160
                  A010400982DS1342-160
                  A010401468DN13450
                  A010544DS1342-32
                  A010700500DS13450
                  A010700504DS13440
                  A010700733DS3344-128
                  A010800725DS1344-51.2
                  A010800814DS13440
                  A010801811DS1342-128
                  A010801927DS1344-40
                  A010802300DS2343-128
                  F1.08801E+110
                  A011827DS1343-160
                  A011001923DS13470
                  A11040142345-42
                  A011200224DS1349-160
                  A011201065DN1343-119.2
                  A011201065DS13412-89.6
                  A020200839DN1342-119.2
                  A020250949DN1345-110
                  G20380476349-1313.82
                  A020400319DS1348-115
                  A020401548DS1346-99
                  B20470661345-120
                  A020501729DN1343-119.2
                  A020501729DS1344-119.2
                  A020601393DS11230
                  A020602323DN1342-350
                  A020702131DS1342-16
                  B210303123430
                  B306703383470
                  G407013803470
                  G407013813480
                  B50200663341-11.68
                  G504011613440
                  G50573348115
                  B506015341-50
                  H508029773420
                  G511026493430
                  G511026523430
                  B51202387344-199
                  H602032673430
                  H602049413490
                  H604034803430
                  B60503883343-110
                  H609029713410
                  B704023053410
                  B706033593440
                  B70702430343-540
                  H70800566343-1470
                  B711031433410
                  B100466344-140
                  B1882349-25
                  B1883342-25
                  B3556344-25
                  B3836344-315
                  B4760346-119
                  B2818342-25
                  B2865342-25
                  B2927344-25
                  B2928344-25
                  B2929346-110
                  B2938346-25
                  H90402220348-214
                  H9.04022E+11-95
                  H90901382342-110
                  H91101822341-1100
                  H1.00402E+110
                  H1.005E+110
                  H1.10303E+11-1851.19
                  H111138341-135
                  H1.20303E+11-526
                  H1.20602E+110
                  D2.06715E+160
                  D2.07846E+160
                  D2.08513E+190
                  D2.09413E+190
                  UNKNOWN2.00932E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00937E+220
                  UNKNOWN2.00939E+222276.52
                  UNKNOWN2.00939E+222276.52
                  H1818841800
                  H1818841800
                  H4201104760
                  F4201145550
                  B1149240
                  F1149240
                  B1205710
                  B1205710
                  G4201263652
                  G4201266149.92
                  H4201266149.92
                  C4201299649
                  C4201299649
                  E42013110
                  E42013110
                  E42013151750
                  E42013151750

                   

                  Reply Actions

                   

                  Jaspreet Mehra

                   

                   

                  Jaspreet Mehra (to Jordy Wegman) 4 hours ago

                   

                   

                  Hello,

                   

                  Please find below sample raw data. Sorry I am unable to PFA file

                     

                  CONNECTION TYPEEmp IdAmount
                  E2801811230
                  E2803451230
                  E280725342-68.4
                  E280725342-68.4
                  E280725342-68.4
                  C1.60473E+32-1008
                  C1.60473E+32-1008
                  G1.60473E+32-1008
                  C1.87063E+32-1588
                  A1.51E+46100
                  C1.00206E+35-360
                  C1.00221E+182320
                  F1.00221E+182320
                  C1.00221E+182320
                  C1.00295E+35-1050
                  F1.00339E+350.06
                  C1.00388E+35-799
                  C1.004E+35-835.68
                  C1.004E+35-835.68
                  C1.004E+35-835.68
                  C1.004E+35-835.68
                  F1.00551E+350
                  A6.23E+46100
                  G1.00639E+350
                  F1.0064E+350
                  A8.63E+46100
                  G8.63E+46100
                  F8.63E+46100
                  UNKNOWN1.01022E+210
                  G1.01037E+311520
                  G1.01078E+35356.47
                  G1.01078E+31-66.62
                  F1.0108E+350
                  A1.09E+47100
                  F1.09E+47100
                  F1.09E+47100
                  A1.09E+47100
                  D1.01345E+210
                  D1.01345E+210
                  F1.01413E+350
                  D1.01652E+210
                  D1.01652E+210
                  D1.01652E+210
                  UNKNOWN1.01652E+210
                  D1.01652E+210
                  D1.01841E+210
                  D1.01842E+210
                  D1.01844E+210
                  D1.01846E+190
                  D1.01851E+210
                  UNKNOWN1.01851E+21-1324.69
                  UNKNOWN1.01859E+221872.83
                  UNKNOWN1.01859E+22832.27
                  D1.0186E+220
                  UNKNOWN1.0186E+22265.11
                  UNKNOWN1.0186E+22265.11
                  D1.0186E+220
                  UNKNOWN1.0186E+22915.03
                  D1.01862E+220
                  A010300268DN1345-134.1
                  A010400499DN1345-159
                  A010400499DS13410-160
                  A010400982DS1342-160
                  A010401468DN13450
                  A010544DS1342-32
                  A010700500DS13450
                  A010700504DS13440
                  A010700733DS3344-128
                  A010800725DS1344-51.2
                  A010800814DS13440
                  A010801811DS1342-128
                  A010801927DS1344-40
                  A010802300DS2343-128
                  F1.08801E+110
                  A011827DS1343-160
                  A011001923DS13470
                  A11040142345-42
                  A011200224DS1349-160
                  A011201065DN1343-119.2
                  A011201065DS13412-89.6
                  A020200839DN1342-119.2
                  A020250949DN1345-110
                  G20380476349-1313.82
                  A020400319DS1348-115
                  A020401548DS1346-99
                  B20470661345-120
                  A020501729DN1343-119.2
                  A020501729DS1344-119.2
                  A020601393DS11230
                  A020602323DN1342-350
                  A020702131DS1342-16
                  B210303123430
                  B306703383470
                  G407013803470
                  G407013813480
                  B50200663341-11.68
                  G504011613440
                  G50573348115
                  B506015341-50
                  H508029773420
                  G511026493430
                  G511026523430
                  B51202387344-199
                  H602032673430
                  H602049413490
                  H604034803430
                  B60503883343-110
                  H609029713410
                  B704023053410
                  B706033593440
                  B70702430343-540
                  H70800566343-1470
                  B711031433410
                  B100466344-140
                  B1882349-25
                  B1883342-25
                  B3556344-25
                  B3836344-315
                  B4760346-119
                  B2818342-25
                  B2865342-25
                  B2927344-25
                  B2928344-25
                  B2929346-110
                  B2938346-25
                  H90402220348-214
                  H9.04022E+11-95
                  H90901382342-110
                  H91101822341-1100
                  H1.00402E+110
                  H1.005E+110
                  H1.10303E+11-1851.19
                  H111138341-135
                  H1.20303E+11-526
                  H1.20602E+110
                  D2.06715E+160
                  D2.07846E+160
                  D2.08513E+190
                  D2.09413E+190
                  UNKNOWN2.00932E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00934E+200
                  UNKNOWN2.00937E+220
                  UNKNOWN2.00939E+222276.52
                  UNKNOWN2.00939E+222276.52
                  H1818841800
                  H1818841800
                  H4201104760
                  F4201145550
                  B1149240
                  F1149240
                  B1205710
                  B1205710
                  G4201263652
                  G4201266149.92
                  H4201266149.92
                  C4201299649
                  C4201299649
                  E42013110
                  E42013110
                  E42013151750
                  E42013151750