Percentile among averages

[AviramWeiss]

Hi everyone,

I have a table with such a structure:

Country	City	Sales
USA	NYC	100
USA	LA	150
USA	Chicago	200
UK	London	50
UK	Manchester	100
France	Paris	200
France	Nice	300

I created a dashboard in which the user can choose the country as a page filter.

One of the components in this page is a Dial Gauge, in which I want to show the average sales of this country, and define the target values as 25th and 75th percentiles (sales in that range will be shown in green, above and below in red).

I created 2 measures and defined them as the gauge's target values:

25th percentile=PercentileX.EXC('Table',[Sales],0.25)

75th percentile=PercentileX.EXC('Table',[Sales],0.75)

This works fine, but I discovered that the average of all countries is always in the range of 25th-75th percentiles.

So what I want to do is calculate the average per country, and then redefine my measures to show the 25th/75th percentiles among the averages.

In the above example, it would be something like:

Country	Average sales
USA	150
UK	75
France	150

And then show percentiles of this table.

Any idea how to do that?

Thank you all in advance,

Aviram

[Greg_Deckler · Accepted solution]

@AviramWeiss Try:

25th percentile = 
  VAR __Table = GROUPBY('Table',[Country],"Average Sales",AVERAGEX(CURRENTGROUP(),[Sales])
  VAR __Result = PERCENTILEX.EXC(__Table,[Average Sales],0.25)
RETURN
  __Result

[AviramWeiss]

Works like a charm. Thank you very much!

For proper documentation, I had to put the GroupBy in a Calculate clause, to add a RemoveFilter (otherwise it always gave the result of the specific chosen country).

Thanks again.