Forum Discussion
How to create Dynamic Decile group column?
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,
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
4 Replies
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 ?
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
Thanks. It would be good for power BI to add this type of functions in the future
