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

T

tusharsharma24

New Member
Joined
Sep 3, 2018
Messages
4
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 (%)Year
Bank of America Corp.75.672017Y
Bank of America Corp.75.012016Y
Bank of America Corp.77.572015Y
Bank of America Corp.81.772014Y
Bank of America Corp.86.452013Y
Bank of America Corp.87.462012Y
Bank of America Corp.93.612011Y
Bank of America Corp.98.082010Y
Bank of America Corp.96.722009Y
Bank of America Corp.108.652008Y
Bank of America Corp.112.672007Y
Bank of America Corp.104.272006Y
Bank of America Corp.92.272005Y
Bank of America Corp.85.462004Y
Bank of America Corp.91.842003Y
Bank of America Corp.92.242002Y
Bank of America Corp.90.352001Y
Bank of America Corp.108.892000Y
JPMorgan Chase & Co.66.262017Y
JPMorgan Chase & Co. 66.332016Y
JPMorgan Chase & Co.66.482015Y
JPMorgan Chase & Co.57.672014Y
JPMorgan Chase & Co.59.412013Y
JPMorgan Chase & Co.63.562012Y
JPMorgan Chase & Co.65.822011Y
JPMorgan Chase & Co.78.132010Y
JPMorgan Chase & Co.69.362009Y
JPMorgan Chase & Co.75.422008Y
JPMorgan Chase & Co.752007Y
JPMorgan Chase & Co.74.452006Y
JPMorgan Chase & Co.80.882005Y
JPMorgan Chase & Co.83.22004Y
JPMorgan Chase & Co.67.242003Y
JPMorgan Chase & Co.70.962002Y
JPMorgan Chase & Co.74.032001Y
JPMorgan Chase & Co.77.342000Y
Wells Fargo & Company72.82017Y
Wells Fargo & Company75.692016Y
Wells Fargo & Company76.162015Y
Wells Fargo & Company75.132014Y
Wells Fargo & Company77.812013Y
Wells Fargo & Company83.922012Y
Wells Fargo & Company89.022011Y
Wells Fargo & Company95.642010Y
Wells Fargo & Company100.242009Y
Wells Fargo & Company113.522008Y
Wells Fargo & Company118.012007Y
Wells Fargo & Company113.872006Y
Wells Fargo & Company111.942005Y
Wells Fargo & Company118.622004Y
Wells Fargo & Company1172003Y
Wells Fargo & Company117.32002Y
Wells Fargo & Company110.882001Y
Wells Fargo & Company104.522000Y
U.S. Bancorp81.632017Y
U.S. Bancorp82.862016Y
U.S. Bancorp87.552015Y
U.S. Bancorp88.942014Y
U.S. Bancorp90.382013Y
U.S. Bancorp92.312012Y
U.S. Bancorp92.892011Y
U.S. Bancorp99.042010Y
U.S. Bancorp108.892009Y
U.S. Bancorp118.082008Y
U.S. Bancorp120.692007Y
U.S. Bancorp117.592006Y
U.S. Bancorp111.852005Y
U.S. Bancorp105.812004Y
U.S. Bancorp100.522003Y
U.S. Bancorp104.222002Y
U.S. Bancorp111.412001Y
U.S. Bancorp129.732000Y
PNC Financial Services Group, Inc.84.142017Y
PNC Financial Services Group, Inc.82.932016Y
PNC Financial Services Group, Inc.83.622015Y
PNC Financial Services Group, Inc.89.162014Y
PNC Financial Services Group, Inc.89.562013Y
PNC Financial Services Group, Inc.88.932012Y
PNC Financial Services Group, Inc.86.182011Y
PNC Financial Services Group, Inc.84.022010Y
PNC Financial Services Group, Inc.85.622009Y

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
How to use continuous If else loop in Excel with
Replies
5
Views
1K
Peter_SSs
Peter_SSs
K
  • Question
VBA to sum columns in blank cells
Replies
4
Views
2K
igold
igold

Forum statistics

Threads
1,215,003
Messages
6,122,655
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top