powerbidaxdata-analysispowerbi-desktopcalculated-columns

Get Percentiles per group in PowerBI

I am trying to calculate percentiles for Bid/Ask prices for a large group of Bond ISINs. Specifically I have my data formatted like below:

Securities PxBid PxAsk PxMid GroupID Q1 Q3
AT0000A04967 113.598 114.198 113.898 1 113.7073 114.0221
AT0000A04967 113.684 114.152 113.918 1 113.7073 114.0221
AT0000A04967 113.878453 114.090701 113.984577 1 113.7073 114.0221
AT0000A04967 113.777 114.239 114.008 1 113.7073 114.0221
AT0000A04967 113.809 114.209 114.009 1 113.7073 114.0221
AT0000A04967 113.53 114.53 114.03 1 113.7073 114.0221
AT0000A04967 113.795 114.295 114.045 1 113.7073 114.0221
AT0000A04967 114.07 114.07 114.07 1 113.7073 114.0221
AT0000A04967 114.1 114.1 114.1 1 113.7073 114.0221
AT0000A04967 114.105 114.185 114.145 1 113.7073 114.0221
AT0000A0U3T4 100.355 100.355 100.355 2 100.2763 100.3445
AT0000A0U3T4 100.257 100.457 100.357 2 100.2763 100.3445
AT0000A0U3T4 100.28 100.435 100.358 2 100.2763 100.3445
AT0000A0U3T4 100.284 100.434 100.359 2 100.2763 100.3445
AT0000A0U3T4 100.275 100.443 100.359 2 100.2763 100.3445
AT0000A0U3T4 98.86 101.86 100.36 2 100.2763 100.3445
AT0000A0U3T4 100.311 100.411 100.361 2 100.2763 100.3445
AT0000A0U3T4 100.313055 100.411003 100.362029 2 100.2763 100.3445
AT0000A0U3T4 100.37 100.37 100.37 2 100.2763 100.3445
AT0000A0U3T4 100.3748 100.3948 100.3848 2 100.2763 100.3445

I want to calculate the 25th and 75th percentile per ISIN, e.g. for the group as shown in the table above. I have tried using the below formula:

Q1 = PERCENTILEX.INC(
ALLSELECTED(CleansedBenchmark[Securities]),
CleansedBenchmark[PxBid],
0.25)

But that just gives me the same bid for each row:

Securities PxBid PxAsk PxMid GroupID Q1
AT0000A04967 113.598 114.198 113.898 1 113.598
AT0000A04967 113.684 114.152 113.918 1 113.684
AT0000A04967 113.878453 114.090701 113.984577 1 113.8785
AT0000A04967 113.777 114.239 114.008 1 113.777
AT0000A04967 113.809 114.209 114.009 1 113.809
AT0000A04967 113.53 114.53 114.03 1 113.53
AT0000A04967 113.795 114.295 114.045 1 113.795
AT0000A04967 114.07 114.07 114.07 1 114.07
AT0000A04967 114.1 114.1 114.1 1 114.1
AT0000A04967 114.105 114.185 114.145 1 114.105
AT0000A0U3T4 100.355 100.355 100.355 2 100.355
AT0000A0U3T4 100.257 100.457 100.357 2 100.257
AT0000A0U3T4 100.28 100.435 100.358 2 100.28
AT0000A0U3T4 100.284 100.434 100.359 2 100.284
AT0000A0U3T4 100.275 100.443 100.359 2 100.275
AT0000A0U3T4 98.86 101.86 100.36 2 98.86
AT0000A0U3T4 100.311 100.411 100.361 2 100.311
AT0000A0U3T4 100.313055 100.411003 100.362029 2 100.3131
AT0000A0U3T4 100.37 100.37 100.37 2 100.37
AT0000A0U3T4 100.3748 100.3948 100.3848 2 100.3748

I'm sure I am missing something silly here, so any help is appreciated! Ideally I want this in the same table, but maybe it's more efficient to create a new table to store the results per ISIN, I'm not sure.

Solution

  • If this is a calculated column you're adding to a table, then use the following code.

    Q1 = 
CALCULATE(
PERCENTILEX.INC( 
    CleansedBenchmark,
    CleansedBenchmark[PxBid],
    0.25)
, ALLEXCEPT(CleansedBenchmark, CleansedBenchmark[Securities]))