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



## tusharsharma24 (Sep 3, 2018)

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. 


Bank_NameLoans/ Deposits (%)YearBank of America Corp.75.672017YBank of America Corp.75.012016YBank of America Corp.77.572015YBank of America Corp.81.772014YBank of America Corp.86.452013YBank of America Corp.87.462012YBank of America Corp.93.612011YBank of America Corp.98.082010YBank of America Corp.96.722009YBank of America Corp.108.652008YBank of America Corp.112.672007YBank of America Corp.104.272006YBank of America Corp.92.272005YBank of America Corp.85.462004YBank of America Corp.91.842003YBank of America Corp.92.242002YBank of America Corp.90.352001YBank of America Corp.108.892000YJPMorgan Chase & Co.66.262017YJPMorgan Chase & Co. 66.332016YJPMorgan Chase & Co.66.482015YJPMorgan Chase & Co.57.672014YJPMorgan Chase & Co.59.412013YJPMorgan Chase & Co.63.562012YJPMorgan Chase & Co.65.822011YJPMorgan Chase & Co.78.132010YJPMorgan Chase & Co.69.362009YJPMorgan Chase & Co.75.422008YJPMorgan Chase & Co.752007YJPMorgan Chase & Co.74.452006YJPMorgan Chase & Co.80.882005YJPMorgan Chase & Co.83.22004YJPMorgan Chase & Co.67.242003YJPMorgan Chase & Co.70.962002YJPMorgan Chase & Co.74.032001YJPMorgan Chase & Co.77.342000YWells Fargo & Company72.82017YWells Fargo & Company75.692016YWells Fargo & Company76.162015YWells Fargo & Company75.132014YWells Fargo & Company77.812013YWells Fargo & Company83.922012YWells Fargo & Company89.022011YWells Fargo & Company95.642010YWells Fargo & Company100.242009YWells Fargo & Company113.522008YWells Fargo & Company118.012007YWells Fargo & Company113.872006YWells Fargo & Company111.942005YWells Fargo & Company118.622004YWells Fargo & Company1172003YWells Fargo & Company117.32002YWells Fargo & Company110.882001YWells Fargo & Company104.522000YU.S. Bancorp81.632017YU.S. Bancorp82.862016YU.S. Bancorp87.552015YU.S. Bancorp88.942014YU.S. Bancorp90.382013YU.S. Bancorp92.312012YU.S. Bancorp92.892011YU.S. Bancorp99.042010YU.S. Bancorp108.892009YU.S. Bancorp118.082008YU.S. Bancorp120.692007YU.S. Bancorp117.592006YU.S. Bancorp111.852005YU.S. Bancorp105.812004YU.S. Bancorp100.522003YU.S. Bancorp104.222002YU.S. Bancorp111.412001YU.S. Bancorp129.732000YPNC Financial Services Group, Inc.84.142017YPNC Financial Services Group, Inc.82.932016YPNC Financial Services Group, Inc.83.622015YPNC Financial Services Group, Inc.89.162014YPNC Financial Services Group, Inc.89.562013YPNC Financial Services Group, Inc.88.932012YPNC Financial Services Group, Inc.86.182011YPNC Financial Services Group, Inc.84.022010YPNC Financial Services Group, Inc.85.622009Y

<colgroup><col><col><col></colgroup><tbody>

</tbody>


----------



## VBA Geek (Sep 10, 2018)

Have you tried using the PERCENTILE*X*.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>


----------

