cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Percentile among averages

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?

Aviram

1 ACCEPTED SOLUTION
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.