How to create Dynamic Deciles for a data set in Power BI ?

[tusharsharma24]

I have the following data set for which I need to divide into decile for a particular year and also want to view the banks in a particular deciles. Is It possible to create this on the basis of a calculated column in Power BI. I used a simple Decile formula, but that doesn't work.

[TABLE="width: 446"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Bank_Name[/TD]
[TD]Loans/ Deposits (%)[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]75.67[/TD]
[TD]2017Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]75.01[/TD]
[TD]2016Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]77.57[/TD]
[TD]2015Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]81.77[/TD]
[TD]2014Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]86.45[/TD]
[TD]2013Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]87.46[/TD]
[TD]2012Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]93.61[/TD]
[TD]2011Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]98.08[/TD]
[TD]2010Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]96.72[/TD]
[TD]2009Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]108.65[/TD]
[TD]2008Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]112.67[/TD]
[TD]2007Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]104.27[/TD]
[TD]2006Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]92.27[/TD]
[TD]2005Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]85.46[/TD]
[TD]2004Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]91.84[/TD]
[TD]2003Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]92.24[/TD]
[TD]2002Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]90.35[/TD]
[TD]2001Y[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD="align: center"]108.89[/TD]
[TD]2000Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]66.26[/TD]
[TD]2017Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co. [/TD]
[TD="align: center"]66.33[/TD]
[TD]2016Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]66.48[/TD]
[TD]2015Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]57.67[/TD]
[TD]2014Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]59.41[/TD]
[TD]2013Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]63.56[/TD]
[TD]2012Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]65.82[/TD]
[TD]2011Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]78.13[/TD]
[TD]2010Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]69.36[/TD]
[TD]2009Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]75.42[/TD]
[TD]2008Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]75[/TD]
[TD]2007Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]74.45[/TD]
[TD]2006Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]80.88[/TD]
[TD]2005Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]83.2[/TD]
[TD]2004Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]67.24[/TD]
[TD]2003Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]70.96[/TD]
[TD]2002Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]74.03[/TD]
[TD]2001Y[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD="align: center"]77.34[/TD]
[TD]2000Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]72.8[/TD]
[TD]2017Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]75.69[/TD]
[TD]2016Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]76.16[/TD]
[TD]2015Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]75.13[/TD]
[TD]2014Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]77.81[/TD]
[TD]2013Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]83.92[/TD]
[TD]2012Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]89.02[/TD]
[TD]2011Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]95.64[/TD]
[TD]2010Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]100.24[/TD]
[TD]2009Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]113.52[/TD]
[TD]2008Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]118.01[/TD]
[TD]2007Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]113.87[/TD]
[TD]2006Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]111.94[/TD]
[TD]2005Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]118.62[/TD]
[TD]2004Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]117[/TD]
[TD]2003Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]117.3[/TD]
[TD]2002Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]110.88[/TD]
[TD]2001Y[/TD]
[/TR]
[TR]
[TD]Wells Fargo & Company[/TD]
[TD="align: center"]104.52[/TD]
[TD]2000Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]81.63[/TD]
[TD]2017Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]82.86[/TD]
[TD]2016Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]87.55[/TD]
[TD]2015Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]88.94[/TD]
[TD]2014Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]90.38[/TD]
[TD]2013Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]92.31[/TD]
[TD]2012Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]92.89[/TD]
[TD]2011Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]99.04[/TD]
[TD]2010Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]108.89[/TD]
[TD]2009Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]118.08[/TD]
[TD]2008Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]120.69[/TD]
[TD]2007Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]117.59[/TD]
[TD]2006Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]111.85[/TD]
[TD]2005Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]105.81[/TD]
[TD]2004Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]100.52[/TD]
[TD]2003Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]104.22[/TD]
[TD]2002Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]111.41[/TD]
[TD]2001Y[/TD]
[/TR]
[TR]
[TD]U.S. Bancorp[/TD]
[TD="align: center"]129.73[/TD]
[TD]2000Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]84.14[/TD]
[TD]2017Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]82.93[/TD]
[TD]2016Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]83.62[/TD]
[TD]2015Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]89.16[/TD]
[TD]2014Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]89.56[/TD]
[TD]2013Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]88.93[/TD]
[TD]2012Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]86.18[/TD]
[TD]2011Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]84.02[/TD]
[TD]2010Y[/TD]
[/TR]
[TR]
[TD]PNC Financial Services Group, Inc.[/TD]
[TD="align: center"]85.62[/TD]
[TD]2009Y[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Have you tried using the PERCENTILEX.INC function ?

you should be able to add a column with something like the below:

=<br><span class="Keyword" style="color:#0070FF">VAR</span> <span class="Variable" style="color:#49b0af">YearTable</span> =<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span> Data, Data[Year] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Year] <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="Keyword" style="color:#0070FF">RETURN</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SWITCH</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">TRUE</span><span class="Parenthesis" style="color:#969696"> (</span><span class="Parenthesis" style="color:#969696">)</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.1</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"1th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.2</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"2th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.3</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"3th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.4</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"4th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.5</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"5th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.6</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"6th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.7</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"7th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.8</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"8th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">0.9</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"9th"</span>,<br>        Data[Loans/ Deposits (%)]<br>            <= <span class="Keyword" style="color:#0070FF">PERCENTILEX.INC</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Variable" style="color:#49b0af">YearTable</span>, Data[Loans/ Deposits (%)], <span class="Number" style="color:#EE7F18">1</span> <span class="Parenthesis" style="color:#969696">)</span>, <span class="StringLiteral" style="color:#D93124">"10th"</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br>