Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
AviramWeiss
Helper I
Helper I

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors