Forum Discussion
Solved
Quartiles Calculated Columns
Hi! I tried really hard to figure this out myself using the forum, but I just can't. Still relatively new to PowerBI. Below is an example of what my data looks like, with my desired calculated co...
hello NewbNeedHelp
this is the output of the code :
this is the output you sent :
Column =
var quartile_1 =
CONVERT(
CALCULATE(
PERCENTILE.INC(Sheet1[Account Contract Value],0.25),
ALLEXCEPT(Sheet1,Sheet1[Segment])
),DOUBLE)
var quartile_2 =
CONVERT(
CALCULATE(
PERCENTILE.INC(Sheet1[Account Contract Value],0.50),
ALLEXCEPT(Sheet1,Sheet1[Segment])
),DOUBLE)
var quartile_3 =
CONVERT(
CALCULATE(
PERCENTILE.INC(Sheet1[Account Contract Value],0.75),
ALLEXCEPT(Sheet1,Sheet1[Segment])
),DOUBLE)
return SWITCH(
TRUE(),
CONVERT(Sheet1[Account Contract Value],double) <= quartile_1 , 1 ,
CONVERT(Sheet1[Account Contract Value],double) >= quartile_1 && CONVERT(Sheet1[Account Contract Value],double) <= quartile_2 , 2 ,
CONVERT(Sheet1[Account Contract Value],double) >= quartile_2 && CONVERT(Sheet1[Account Contract Value],double) <= quartile_3 ,3 ,
CONVERT(Sheet1[Account Contract Value],double) > quartile_3 , 4
)
please try this caluclated column and tell me if it works for you
best regards.
for the second calculated column,
you just need to add in the ALLEXCEPT function, THE COLUMN name of : account group ,
ALLEXCEPT(Sheet1,Sheet1[Segment],Sheet1[account group])
everything else stay the same.