Forum Discussion
Calculate Percentile Splitting Data Into One Column Instead of Many
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.
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
3 Replies
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
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!!!
Hi,
Very confused about what you want. WIth your measure
percentile = PERCENTILE.INC(Data[total],1)
i get this
What is the problem?
