3 Replies Latest reply: Jul 9, 2018 12:48 AM by Olivier GAUTIER RSS

    Text field average

    Liam Lucas

      Transportation Mode

      Identifier

      Day

      Regularity

      Train

      2

      1/6/2018

      1

      Train

      3

      1/6/2018

      1

      Train

      7

      2/17/2018

      1

      Train

      11

      2/17/2018

      1

      Train

      22

      2/17/2018

      1

      Train

      22

      3/17/2018

      1

      Plane

      1

      3/17/2018

      1

      Plane

      1

      3/17/2018

      1

      Plane

      4

      3/17/2018

      1

      Plane

      9

      3/17/2018

      1

      Plane

      9

      3/17/2018

      1

      Plane

      19

      3/17/2018

      1

      Plane

      21

      3/17/2018

      1

      Car

      5

      1/6/2018

      1

      Car

      5

      1/6/2018

      1

      Car

      5

      1/6/2018

      1

      Car

      10

      1/6/2018

      1

      Car

      13

      1/6/2018

      1

      Car

      18

      1/6/2018

      1

      Car

      20

      2/17/2018

      1

      Car

      20

      3/17/2018

      1

      Boat

      6

      1/6/2018

      1

      Boat

      8

      2/17/2018

      1

      Boat

      12

      2/17/2018

      1

      Boat

      14

      2/17/2018

      1

      Boat

      15

      2/17/2018

      1

      Boat

      16

      3/17/2018

      1

      Boat

      17

      3/17/2018

      1

       

      In my sheet I have a table with Transportation Mode as the dimension.  I want to include a measure that calculates the average number of times each Transportation Mode (Train, Plane, Car, Boat) occurs if each classifier is only counted once. 

       

      Here is my formula but it is not working, can you assist?

       

      Avg(Aggr((Transportation Mode),Distinct[Classifier])

       

      This is the result I am trying to get

       

      Transportation Mode

      1/6/2018

      2/17/2018

      3/17/2018

      AVG

      Train

      2

      3

      1

      1. 2.0

      Boat

      1

      4

      2

      1. 2.3

      Car

      6

      1

      1

      1. 2.7

      Plane

      0

      0

      7

      1. 2.3
        • Re: Text field average
          Olivier GAUTIER

          Hi,

           

          just use this :

          Train : 2,3,7,11,22

          = 45/5 = 9

           

          is it what you want ?

           

           

          regards

            • Re: Text field average
              Liam Lucas

              Hi Olivier,  your question helped me see how I could improve my question, so I made the change and posted above.  I am trying to turn this...

               

              Transportation Mode

              Identifier

              Day

              Regularity

              Train

              2

              1/6/2018

              1

              Train

              3

              1/6/2018

              1

              Train

              7

              2/17/2018

              1

              Train

              11

              2/17/2018

              1

              Train

              22

              2/17/2018

              1

              Train

              22

              3/17/2018

              1

              Plane

              1

              3/17/2018

              1

              Plane

              1

              3/17/2018

              1

              Plane

              4

              3/17/2018

              1

              Plane

              9

              3/17/2018

              1

              Plane

              9

              3/17/2018

              1

              Plane

              19

              3/17/2018

              1

              Plane

              21

              3/17/2018

              1

              Car

              5

              1/6/2018

              1

              Car

              5

              1/6/2018

              1

              Car

              5

              1/6/2018

              1

              Car

              10

              1/6/2018

              1

              Car

              13

              1/6/2018

              1

              Car

              18

              1/6/2018

              1

              Car

              20

              2/17/2018

              1

              Car

              20

              3/17/2018

              1

              Boat

              6

              1/6/2018

              1

              Boat

              8

              2/17/2018

              1

              Boat

              12

              2/17/2018

              1

              Boat

              14

              2/17/2018

              1

              Boat

              15

              2/17/2018

              1

              Boat

              16

              3/17/2018

              1

              Boat

              17

              3/17/2018

              1

               

              into this...

               

              Transportation Mode

              1/6/2018

              2/17/2018

              3/17/2018

              AVG

              Train

              2

              3

              1

              1. 2.0

              Boat

              1

              4

              2

              1. 2.3

              Car

              6

              1

              1

              1. 2.7

              Plane

              0

              0

              7

              1. 2.3
                • Re: Text field average
                  Olivier GAUTIER

                  So

                   

                  make a pvot table with dimensions Transportation and Day

                   

                  in expression :

                  if(SecondaryDimensionality()=1,  count( Identifier),sum(aggr(count(Identifier), [Transportation Mode],Day ))/ count(total distinct  Day))

                   

                  with subtotal for Day

                  secondarydim is used because subtotal is not the same as expression

                   

                  just add regularity too

                   

                  regards