Percentile exe Calculation by summarize or Grouping

[dmuralli1829]

Hi 

I want to caculate percentile .exe for the lab parameters. 

I have the applied the following dax

Parameter to dynamically Adjust Lower and Upper Values:

Lower Limit = PERCENTILEX.EXC(ALL('Lab'), 'Lab'[Value],[Lower Percentile Value])

Higher Limit = PERCENTILEX.EXC(ALL('Lab'), 'Lab'[Value],[Higher percentile value])

Lower Parameter = GENERATESERIES(1, 5, 1)

Parameter L Value = SELECTEDVALUE('Parameter L'[Parameter L])

Lower Percentile Value = 'Parameter L'[Parameter L Value]/100

Parameter UL = GENERATESERIES(10, 99, 1)

Parameter UL Value = SELECTEDVALUE('Parameter UL'[Parameter UL])

Higher percentile value = 'Parameter UL'[Parameter UL Value]/100

I have set up slicer for Lab parameter, when i select ALT, the lower Limit and Upper limit is expected to calcualted for selected ALT Lab parameter instead it is considering and selecting values for all lab Parameter. The values are displayed in Higher and Lower Limit column in the table.

I have copied partial table for your refrence.

Lab Table:

Lab Parameter	Visit Name	Site Number	Subject Number	Value	Higher Limit	Lower Limit
ALT	Day 1	5015	5015001	117	90	3.8
AST	Day 1	5015	5015001	77	90	3.8
Basophils (%)	Day 1	5015	5015001	0.5	90	3.8
Basophils (Abs)	Day 1	5015	5015001	20.9	90	3.8
Blood Urea Nitrogen	Day 1	5015	5015001	13.2	90	3.8
Creatinine	Day 1	5015	5015001	0.5	90	3.8
D-Dimer	Day 1	5015	5015001	0.957	90	3.8
eGFR	Day 1	5015	5015001	116.89	90	3.8
Eosinophils (%)	Day 1	5015	5015001	2.2	90	3.8
Eosinophils (Abs)	Day 1	5015	5015001	92	90	3.8
HbA1c	Day 1	5015	5015001	8.3	90	3.8
Hematocrit	Day 1	5015	5015001	34.2	90	3.8
Hemoglobin	Day 1	5015	5015001	10.9	90	3.8
HS Troponin	Day 1	5015	5015001	0.01	90	3.8
hsCRP	Day 1	5015	5015001	70	90	3.8
Lactate Dehydrogenase	Day 1	5015	5015001	394	90	3.8
Lymphocytes (%)	Day 1	5015	5015001	41.6	90	3.8
Lymphocytes (Abs)	Day 1	5015	5015001	1739	90	3.8
Monocytes (%)	Day 1	5015	5015001	8.6	90	3.8
Monocytes (Abs)	Day 1	5015	5015001	359	90	3.8

I want to calculate the percentile.exe. values to display the lower and upper limit for the selection of lab parameter via slicer.

your support and help is much appreciated

regards

MD

[v-deddai1-msft · Accepted solution]

Hi @dmuralli1829 ,

You can try to use allselected in your measure:

Lower Limit = PERCENTILEX.EXC(ALLSELECTED('Lab'), 'Lab'[Value],[Lower Percentile Value])
Higher Limit = PERCENTILEX.EXC(ALLSELECTED('Lab'), 'Lab'[Value],[Higher percentile value])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

You can't use ALL around a table if you want the measure to be responsive to your selections. ALL effectively IGNORES all filters that may exist on the table or come to the table from outside.