Quartile values calculation which is further dependent on percentile slicer

[Lakshi]

I am working on building a box and whisker chart in power bi.

On the left we have two filters, filter 1 and filter 2. On the box and whisker chart, i want to show Quartile 1, Quartile 2, Quartile 3 and average value on the chart.

The Quartile 1, Quartile 2, Quartile 3 and average value is calculated by storing the value of filter 1/filter 2 in a variable. Further, i have also assigned percentile to the values.

Please refer to the dax below.

Average =

var selectedvalue_ = SELECTEDVALUE('1'[Metrics 1])
var selectedvalue_deno = SELECTEDVALUE('2'[Metrics 1])
var paramin = Minimum[Minimum Value]
var paramax = Maximum[Maximum Value]

var tbl = summarize(Data, Data[ID], Data[Date],Data[Umbrella Industry],"ED",CALCULATE(SUM(Data[Value]),Data[Metrics]=selectedvalue_),"EDII",CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Metrics]=selectedvalue_deno)),"yr",year(min(Data[Date])))

var tbl_ = ADDCOLUMNS(tbl,"num/deno",DIVIDE([EDII],[ED],0))

var tbl2 = Addcolumns(tbl_, "PercentileCalc", var yrr = Data[Umbrella Industry] var thisrowindex = [num/deno]
Return
CountAx(filter(filter(tbl_, Data[Umbrella Industry] = yrr), [num/deno] <= thisrowindex),[num/deno])/
countrows(filter(tbl_,Data[Umbrella Industry]=yrr)))
return
AVERAGEX(tbl2, IF([PercentileCalc]>=paramin && [PercentileCalc] <=paramax,[num/deno]))

Q1 =

var selectedvalue_ = SELECTEDVALUE('1'[Metrics 1])
var selectedvalue_deno = SELECTEDVALUE('2'[Metrics 1])
var paramin = Minimum[Minimum Value]
var paramax = Maximum[Maximum Value]

var tbl = summarize(Data, Data[ID], Data[Date],Data[Umbrella Industry],"ED",CALCULATE(SUM(Data[Value]),Data[Metrics]=selectedvalue_),"EDII",CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Metrics]=selectedvalue_deno)),"yr",year(min(Data[Date])))

var tbl_ = ADDCOLUMNS(tbl,"num/deno",DIVIDE([EDII],[ED],0))

var tbl2 = Addcolumns(tbl_, "PercentileCalc", var yrr = Data[Umbrella Industry] var thisrowindex = [num/deno]
Return
CountAx(filter(filter(tbl_, Data[Umbrella Industry] = yrr), [num/deno] <= thisrowindex),[num/deno])/
countrows(filter(tbl_,Data[Umbrella Industry]=yrr)))
return
COUNTX(tbl2, IF([PercentileCalc]>=paramin && [PercentileCalc] <=paramax,[num/deno]))

I have created a parameter using Minimum = GENERATESERIES(0, 1.1, 0.0001) and maximum = GENERATESERIES(0, 1.1, 0.0001) which i am using to control the percentile value.

I am getting correct output for all the parameters if i am using 0-100 percentile but the moment i change percentile the output for average is coming correct but for quartiles the output is coming wrong. I have attaching the .pbix file for your reference.

Would be great if someone can help.

@PowerBI @Expert @dax 

[Anonymous]

Hi @Lakshi ,

I didn't find the link, could you please check again, thanks.
I noticed that the third parameter in your code is a floating-point value, please check if the following is causing the problem.

Generating a series of numbers in DAX - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group