topic Re: help with quartile measurement in DAX Commands and Tips

help with quartile measurement

Anonymous — Mon, 08 Feb 2021 19:19:46 GMT

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

Re: help with quartile measurement

JW_van_Holst — Mon, 08 Feb 2021 18:39:19 GMT

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 )

Re: help with quartile measurement

Anonymous — Mon, 08 Feb 2021 18:50:16 GMT

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

Re: help with quartile measurement

JW_van_Holst — Wed, 10 Feb 2021 16:08:04 GMT

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

Re: help with quartile measurement

Anonymous — Wed, 10 Feb 2021 16:31:59 GMT

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