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.

Anonymous
Not applicable

## help with quartile measurement

Hello,
I hope the message finds you well.

I created a measure to find the completed sales rate.

I need to make a quartile to show the 25% salespeople with the worst result (4th quartile),

the other 25% with the second worst rate (3rd quartile),

the 25% with the second best rate (2nd quartile)

and the 25% with the best rate (1st quartile).

I tried the measure below, but it didn't work, does anyone have an idea to help?

Quartile =
var FirstQ = PERCENTILEX.INC(Quartile,[% Rate],0.25)
var SecondQ = PERCENTILEX.INC(Quartile,[% Rate],0.50)
var ThirdQ = PERCENTILEX.INC(Quartile,[% Rate],0.75)
var ThisVal = [% Rate]
return
IF(HASONEVALUE(Quartile,[% Rate]),
IF(ThisVal <= FirstQ, 1,
IF(ThisVal >FirstQ && ThisVal <= SecondQ, 2,
IF(ThisVal >SecondQ && ThisVal <=ThirdQ,3, 4)
)
)
)

4 REPLIES 4
Resolver IV

Try ALL() around your tabel. I tried this on a dummy table.

``````_test =
var __1Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],0.25)
var __2Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],0.5)
VAR __3Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],0.75)
VAR __4Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],1)
VAR __value = SELECTEDVALUE(Query1[Value])
RETURN
SWITCH(
TRUE(),
__value <= __1Q, 1,
__value <= __2Q, 2,
__value <= __3Q, 3,
4
)``````

Anonymous
Not applicable

Dear, good afternoon.

I couldn't get it to work..
It returns incorrect values ​​to me.

you can do it in the attached file?

I need to calculate the rate that is CONFIRMED SALE / SOLICITATION (it is the measure (% Rate in the file).

On this "rate" I need to classify the best 25% as Q1, Q2 the next 25% and so on until Q4.

This measurement does not work when I include the% Rate measurement, even though creating a calculated column and including it in your example, the classification does not do what I need.

Resolver IV

Try ALL() around your table. I tried this on a dummy table.

``````_test =
var __1Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],0.25)
var __2Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],0.5)
VAR __3Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],0.75)
VAR __4Q = PERCENTILEX.INC(ALL(Query1), Query1[Value],1)
VAR __value = SELECTEDVALUE(Query1[Value])
RETURN
SWITCH(
TRUE(),
__value <= __1Q, 1,
__value <= __2Q, 2,
__value <= __3Q, 3,
4
)``````

Anonymous
Not applicable

Dear @JW_van_Holst

Thanks for the feedback, but I was unable to apply the code.

Can you show me in the file how it would look?

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors