CALCULATE QUARTILES WITH FILTERS - POWER BI

user_15318675 0
Viewed 1166

I am trying to put together a DAX statement in Power BI to calculate the quartiles of a table according to some filters. I did a generic sentence however, I have problems to be able to assign the filters to it. The table structure is:

Campaing management group id_oper name_oper nom_sup tickets
convergente bo tecnico convergente 0000000 operador1 supervisor1 500
convergente bo tecnico convergente 11111111 operador2 supervisor1 200
convergente bo tecnico convergente 22222222 operador3 supervisor1 80
convergente bo tecnico convergente 33333333 operador4 supervisor1 300
despacho bo despacho averias 44444444 operador5 supervisor2 1500
despacho bo despacho averias 55555555 operador6 supervisor2 500
despacho bo despacho averias 66666666 operador7 supervisor2 30
despacho bo despacho averias 77777777 operador8 supervisor2 1000
multiskill bo provision multiskill 88888888 operador9 supervisor3 20
multiskill bo provision multiskill 99999999 operador10 supervisor3 5
multiskill bo provision multiskill 12345678 operador11 supervisor3 80
multiskill bo provision multiskill 87654321 operador12 supervisor3 3

And the Power BI query that i'm using is this:

Quantile = 
IF(TBL_CUARTIL[tickets]<= PERCENTILE.EXC(TBL_CUARTIL[tickets],0.25),"Q4",
IF(AND(TBL_CUARTIL[tickets]> PERCENTILE.EXC(TBL_CUARTIL[tickets],0.25),TBL_CUARTIL[tickets]<= PERCENTILE.EXC(TBL_CUARTIL[tickets],0.5)),"Q3",
IF(AND(TBL_CUARTIL[tickets]> PERCENTILE.EXC(TBL_CUARTIL[tickets],0.5),TBL_CUARTIL[tickets]<= PERCENTILE.EXC(TBL_CUARTIL[tickets],0.75)),"Q2",
IF(TBL_CUARTIL[tickets]> PERCENTILE.EXC(TBL_CUARTIL[tickets],0.75),"Q1"))))

The query calculates the quartile by operator and it works without problems, but it calculates them on the total of records, and I would like to know how I can calculate the same but with data filters, for example filters of the campaign column.

I would appreciate your help with this problem, and I hope I have explained ... thank you very much

dax powerbi
1 Answers

Use New measure:

Quantile=
var cur_val = max(TBL_CUARTIL[tickets])  
return IF(cur_val< PERCENTILEX.EXC(ALLSELECTED(TBL_CUARTIL), TBL_CUARTIL[tickets], 0.25),"Q4",
IF(AND(cur_val> PERCENTILEX.EXC(ALLSELECTED(TBL_CUARTIL), TBL_CUARTIL[tickets], 0.25),cur_val<= PERCENTILEX.EXC(ALLSELECTED(TBL_CUARTIL), TBL_CUARTIL[tickets], 0.5)),"Q3",
IF(AND(cur_val> PERCENTILEX.EXC(ALLSELECTED(TBL_CUARTIL), TBL_CUARTIL[tickets], 0.5),cur_val<= PERCENTILEX.EXC(ALLSELECTED(TBL_CUARTIL), TBL_CUARTIL[tickets], 0.75)),"Q2",
IF(cur_val> PERCENTILEX.EXC(ALLSELECTED(TBL_CUARTIL), TBL_CUARTIL[tickets], 0.75),"Q1"))))
user_79402920
Related