5 Replies Latest reply on Sep 28, 2018 8:23 AM by swaroop.gantela

# Average of a measure across all values of a dimension

I am creating visualizations for the analytics data of a racing game.

What I want to know is this: out of all the races on a given track, how often are players achieving 1st place, 2nd place, etc.?

Above, you can see that I am counting the number of times each place was achieved on each track. However, players can choose their tracks and tracks unlock slowly over time, so some tracks will naturally have a higher number of races in general, which makes it difficult to compare the 1st place values of each track to the other tracks.

Looking at the top value on the y-axis, "BeachB", you can see that there are 51 1st place races. What I'd like to see here instead is that number over the total number of races in BeachB: [1st] / ( [1st]+[2nd]+[3rd]+[4th]+[5th]+[6th]+[7th]+[8th] ) = 51/119 = 0.43

The values I need to see for BeachB are: 0.43, 0.18, 0.13, 0.06, 0.06, 0.07, 0.03, 0.04

I found this post, which DOES NOT solve my problem:

• ###### 1. Re: Average of a measure across all values of a dimension

Hi Phipps,

Create a calculated field like below

CNT(Number of Records)/

{Fixed track: CNT([Number of records]}}

This would give the correct result

If any filters in the filters --> add them to context

BR,

NB

1 of 1 people found this helpful
• ###### 2. Re: Average of a measure across all values of a dimension

Not sure if I got the syntax right on this. Mine looks like this: COUNT( [Number of Records] ) / {FIXED [Track]:COUNT([Number of Records])}

Also, I do in fact have filters, but I have no idea what this means:

If any filters in the filters --> add them to context

What is "context" and how do I add to it?

• ###### 3. Re: Average of a measure across all values of a dimension

Okay, I figured out how to "Add to Context" and the syntax above still does not work.

• ###### 4. Re: Average of a measure across all values of a dimension

It works! The final syntax I used was: COUNT( [Number of Records] ) / SUM({FIXED [Track]:COUNT([Number of Records])})

I wrapped the FIXED statement in a SUM function, because when mousing over the '/', the tooltip read "Cannot mix aggregate and non-aggregate arguments with this function"

• ###### 5. Re: Average of a measure across all values of a dimension

Ian,

Another version of the same that more explicitly spells it out:

[CountPerPlacePerTrack]/[CountPerTrack]

which is

{ FIXED [Track],[Game Place]:SUM([Number of Records])} / { FIXED [Track]:SUM([Number of Records])}