Forum Discussion

AviramWeiss's avatar
AviramWeiss
Icon for Helper I rankHelper I
3 years ago
Solved

Percentile among averages

Hi everyone,

 

I have a table with such a structure:

 

Country    City    Sales    
USANYC100
USALA150
USAChicago200
UKLondon50
UKManchester100
FranceParis200
FranceNice300

 

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   
USA150
UK75
France150

 

And then show percentiles of this table.

 

Any idea how to do that?

 

Thank you all in advance,

Aviram

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      AviramWeiss
      Icon for Helper I rankHelper I
      3 years ago

      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.