Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Darwinm
Frequent Visitor

Need help with creating a measure to calculate roll rate % of delinquent accounts

Hi folks, 

I am currently stuck in a situation where I am trying to calculate the roll rate % of delinquent accounts in Power BI. I have delinquency listed out in catergories: 1-30, 31-60, 61-90, 90+ and so on along with dates. I want to create a measure which would return the Roll Rate % value for each category (1-30, 31-60, 61-90, 90+ and so on) based on the amounts. I was able to calculate them in Excel and have tried several measures in Power BI but no luck.

For instance, to calculate 1-30 roll rate%: I divided 1-30 amount of current month by Current amount of previous month. Please refer to the screenshots to see the sample calculation. 

Also, I am attaching a table with sample dataset for reference. I would highly appreciate any kind of assistance with this. 

Thank you in advance! 



Calculation SS1.pngCalculation SS2.png

DateCurrent1-301-30 Roll rate %31-6031-60 Roll rate %61-9061-90 Roll rate %91+ 91+ roll rate %
2019-01-315700069000 124374 38732 8686 
2019-02-281000004500079%39848355775%2354619%54366140%
2019-03-31157000800000800%48757108%557651%67676287%
2019-04-30400700700000446%56453571%323423663%77667139%
2019-05-31500900478096119%424546%57657561021%65672%
2019-06-3080070034545369%46456797%45344107%74550%
2019-07-319001002335346292%78768762280%76872%67765149%
2019-08-31100140025356428%55546624%6656778%56363733%
2019-09-30112000023355323%67676872669%425468%7656712%




2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFZjoNADETvwneQ3C5vfZYo97/GeIEJEUhuNX6uKvN+H0xrn7ROrON1qBNRVttTFwtc8oBwcNawsOPzujE+OaqNaPpFp2KHBDRPDBWryQJ7khjBdSvG/4hw9QLUrQaY5/MA5US3EXn3+w2qibYkGMKoT27mD1SvkER7pDxolzlhUen4pq51Y/pD2ojGLYrs15KQFDXv5JAa4aL6AH0kU281yEA2loKHW751ytpBK/AXjWtDicqwGc/apuZO22XZ7GUZDA92j9+1+FpO6bbhXmfrCc+f8cn6+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Current = _t, #"1-30" = _t, #"31-60" = _t, #"61-90" = _t, #"91+ " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Current", type number}, {"1-30", type number}, {"31-60", type number}, {"61-90", type number}, {"91+ ", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "1-30 %", each try [#"1-30"]/#"Changed Type"[Current]{[Index]-1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "31-60 %", each try [#"31-60"]/#"Changed Type"[#"1-30"]{[Index]-1} otherwise null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "61-90 %", each try [#"61-90"]/#"Changed Type"[#"31-60"]{[Index]-1} otherwise null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "91+ %", each try [#"91+ "]/#"Changed Type"[#"61-90"]{[Index]-1} otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"1-30 %", Percentage.Type}, {"31-60 %", Percentage.Type}, {"61-90 %", Percentage.Type}, {"91+ %", Percentage.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, 

Thank you so much for providing guidance. I tried this and it worked just fine :). However, I want the values to change accordingly to the filters ( such as Lending partners, Provinces, etc.) I choose, is there a solution to make it dynamic according to the chosen filters? For instance, in excel I had applied filters in the pivot table and when I selected different dropdown options through filters, the output value changed accordingly. 

Highly appreciate your assistance! 
Thank you! 

"However, I want the values to change accordingly to the filters ( such as Lending partners, Provinces, etc.) "

 

Please provide sanitized sample data that fully covers your issue.


Actually, never mind I finally figured it out. Utilized filter function to filter out each days_delq and calculate from there on. 

I am so thankful to both of you for providing guidance with my situation. Kudos! :))

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFZjoNADETvwneQ3C5vfZYo97/GeIEJEUhuNX6uKvN+H0xrn7ROrON1qBNRVttTFwtc8oBwcNawsOPzujE+OaqNaPpFp2KHBDRPDBWryQJ7khjBdSvG/4hw9QLUrQaY5/MA5US3EXn3+w2qibYkGMKoT27mD1SvkER7pDxolzlhUen4pq51Y/pD2ojGLYrs15KQFDXv5JAa4aL6AH0kU281yEA2loKHW751ytpBK/AXjWtDicqwGc/apuZO22XZ7GUZDA92j9+1+FpO6bbhXmfrCc+f8cn6+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Current = _t, #"1-30" = _t, #"31-60" = _t, #"61-90" = _t, #"91+ " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Current", type number}, {"1-30", type number}, {"31-60", type number}, {"61-90", type number}, {"91+ ", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "1-30 %", each try [#"1-30"]/#"Changed Type"[Current]{[Index]-1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "31-60 %", each try [#"31-60"]/#"Changed Type"[#"1-30"]{[Index]-1} otherwise null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "61-90 %", each try [#"61-90"]/#"Changed Type"[#"31-60"]{[Index]-1} otherwise null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "91+ %", each try [#"91+ "]/#"Changed Type"[#"61-90"]{[Index]-1} otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"1-30 %", Percentage.Type}, {"31-60 %", Percentage.Type}, {"61-90 %", Percentage.Type}, {"91+ %", Percentage.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Hi, 

First I would like to thank you for providing assitance in my case. 
When I tried to paste the above query, I was encounted with an error message. I am attaching a screenshot of the message. Also, my reference table is: 'Data' (seen in the screenshot), so I copied the source query from there and pasted it instead of " 

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFZjoNADETvwneQ3C5vfZYo97/GeIEJEUhuNX6uKvN+H0xrn7ROrON1qBNRVttTFwtc8oBwcNawsOPzujE+OaqNaPpFp2KHBDRPDBWryQJ7khjBdSvG/4hw9QLUrQaY5/MA5US3EXn3+w2qibYkGMKoT27mD1SvkER7pDxolzlhUen4pq51Y/pD2ojGLYrs15KQFDXv5JAa4aL6AH0kU281yEA2loKHW751ytpBK/AXjWtDicqwGc/apuZO22XZ7GUZDA92j9+1+FpO6bbhXmfrCc+f8cn6+QM=", BinaryEncoding.Base64), Compression.Deflate))

, do you think the source I put in is accurate? I would be very grateful to you if you could help me figure this out as I have been scratching my head with for days now. 
Thank you! Roll Rate error.png

In line 4 replace "Source" with "Data_Sheet"

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors