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
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?

 

Link. arquivo.pbi 

 

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)
)
)
)

 

help_menasure_quartile.PNG

 

 

table.PNG

4 REPLIES 4
JW_van_Holst
Resolver IV
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.

 

link powerbi file. https://drive.google.com/file/d/1yG9TXrUWeDm_LyHiEqEmshu89YPx3JQl/view

JW_van_Holst
Resolver IV
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?

File link in pbi.

 

https://drive.google.com/file/d/1yG9TXrUWeDm_LyHiEqEmshu89YPx3JQl/view?usp=sharing 

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors