Text field average

[liamlucas]

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	2.0
Boat	1	4	2	2.3
Car	6	1	1	2.7
Plane	0	0	7	2.3

[ogautier62]

Hi,

just use this :

Train : 2,3,7,11,22

= 45/5 = 9

is it what you want ?

regards

[liamlucas]

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	2.0
Boat	1	4	2	2.3
Car	6	1	1	2.7
Plane	0	0	7	2.3

[ogautier62 · Accepted solution]

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