Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
|
Boat | 1 | 4 | 2 |
|
Car | 6 | 1 | 1 |
|
Plane | 0 | 0 | 7 |
|
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
Hi,
just use this :
Train : 2,3,7,11,22
= 45/5 = 9
is it what you want ?
regards
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 |
|
Boat | 1 | 4 | 2 |
|
Car | 6 | 1 | 1 |
|
Plane | 0 | 0 | 7 |
|
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