How to create Dynamic Decile group column?

[jiangchuan23]

Hey All,

I was trying to create a dynamic decile variable to group credit score. Here is the column formula I created

Column = IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.1),1
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.2),2
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.3),3
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.4),4
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.5),5
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.6),6
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.7),7
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.8),8
,IF(gains_total[CBR_SCR]<PERCENTILE.EXC(gains_total[CBR_SCR],0.9),9,10
)))))))))

Here is the visual I am trying to create here:

As you can see the table above. It worked well for the Overall dataset. It grouped burea score into 10 deciles. each declie had roughly same nunber of counts. However, When I apply the filter to the data. it didn't recalculate declie for the underlying data.

Check snapshot below:

number of counts in each decile are not equal. Do you anybody know how to make the decile group dynamic based on filter you selected ?

Thanks,

[v-yulgu-msft · Accepted solution]

Hi @jiangchuan23,

Since the new declie column is a calculated column, whose values are initialize once it is created. Its value won't be dynamically changed depend on slicer selection.

Regards,

Yuliana Gu

[jiangchuan23]

Thanks. It would be good for power BI to add  this type of functions in the future

[Anonymous]

There are way too many things that seems so obviously needed in Power BI that drive me crazy... just like this!

[jiangchuan23]

it sounds like tableau have a way to do it. 

https://community.tableau.com/thread/224377

Do somebody know how I can replicate this in power BI ?