Calculate Percentile Splitting Data Into One Column Instead of Many

[Vinagre01]

I have a table with the following template:

cisp	month	type	total
1	Jan	A	15
2	Jan	A	20
1	Jan	B	30
2	Jan	B	40
1	Feb	A	50
2	Feb	A	60
1	Feb	B	70
2	Feb	B	80

If I do a simple percentile calculation like:

percentile = PERCENTILE.INC(BaseDPEvolucaoMensalCisp[total], 1)

I will logically obtain 80 as a result.

What I'm trying to accomplish is to have my percentiles calculated taking into account only a CISP column segregation, meaning that the percentile would have to be calculated from a table like this:

cisp	total
1	165
2	200

Then logically, my percentile calculation would return 200.

What I have tried is calculating the percentile from a calculated DAX table like this:

Summ_table = 
SUMMARIZE(
    BaseDPEvolucaoMensalCisp, 
    BaseDPEvolucaoMensalCisp[cisp], 
    "Total", SUMX(BaseDPEvolucaoMensalCisp,BaseDPEvolucaoMensalCisp[total])
)

However the problem is that, even if I create a one to many relationship, this newly calculated table won't be recalculated in case I filter the original "BaseDPEvolucaoMensalCisp" table (and it seems like this is intended behavior). This would be really important as I will need to recalculate my percentile with a "month" and/or "type" filter.

Example with "type" filter (B):

cisp	total
1	100
2	120

Then my percentile calculation would return 120.

I have tried both the forementioned method of calculating a new DAX table and then calculating the percentile, as well as playing a lot with the CALCULATE function, but I don't seem to get it right.

Thank you for your help.

[Ashish_Mathur]

Hi,

Very confused about what you want.  WIth your measure

percentile = PERCENTILE.INC(Data[total],1)

i get this

What is the problem?

[Anonymous · Accepted solution]

Hi  @Vinagre01 ,

Here are the steps you can follow：

1. Create measure.

Measure = 
var _select=SELECTEDVALUE('Table'[type])
return
var _table1=
FILTER(
    ALL('Table'),[type]=_select)
var _table2=
SUMMARIZE(
    _table1,[cisp],"Value",SUM('Table'[total]))

RETURN
PERCENTILEX.INC(_table2,[Value],1)

2. Result:

Best Regards,

Liu Yang

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

[Vinagre01]

Amazing @Anonymous !

I had to tweak a bit because I might need to use multiple values as a filter, so in the end it looked something like this:

Measure = 
var _select=VALUES('Table'[type])
return
var _table1=
FILTER(
    ALL('Table'),[type] IN _select)
var _table2=
SUMMARIZE(
    _table1,[cisp],"Value",SUM('Table'[total]))

RETURN
PERCENTILEX.INC(_table2,[Value],1)

Thanks a lot!!!