Compare One User to All (Parent, Subset, AllNext, AllPrevious?)

Hi, I'm trying to compare one person's results for one task, to everyone's results for the same task.


*Example attached. Screenshot attached.*

Name - Food - Time to Eat

Tom - Apple - 30secs

John - Apple - 45secs

Tom - Orange - 60secs

Sally - Orange - 74secs

etc etc etc x100,000 rows


I need to be able to filter by Name (Tom), then Food (apple), then it shows 1 person's (Tom) avg eating time (for an apple), compared to everybody's (3 ppl) avg eating time for that food (apple). 


Use subsets? Was almost right, but it shows all data (5 types of food, instead of 1). 

With parent? Avg([TimetoEat]) OVER (Parent([Axis.Color]))

With Allprevious? Avg([TimetoEat]) THEN Avg([TimetoEat]) OVER (AllPrevious([Axis.X]))

Hierarchies? Name-Food-TimetoEat? 

Also trying to do this without Trellis'ing


Any help would be appreciated!


Image icon 3.png101.97 KB
Binary Data example.dxp200.56 KB

(3) Answers