Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Date | Current | 1-30 | 1-30 Roll rate % | 31-60 | 31-60 Roll rate % | 61-90 | 61-90 Roll rate % | 91+ | 91+ roll rate % |
2019-01-31 | 57000 | 69000 | 124374 | 38732 | 8686 | ||||
2019-02-28 | 100000 | 45000 | 79% | 3984835 | 5775% | 23546 | 19% | 54366 | 140% |
2019-03-31 | 157000 | 800000 | 800% | 48757 | 108% | 55765 | 1% | 67676 | 287% |
2019-04-30 | 400700 | 700000 | 446% | 564535 | 71% | 323423 | 663% | 77667 | 139% |
2019-05-31 | 500900 | 478096 | 119% | 42454 | 6% | 5765756 | 1021% | 6567 | 2% |
2019-06-30 | 800700 | 345453 | 69% | 464567 | 97% | 45344 | 107% | 7455 | 0% |
2019-07-31 | 900100 | 2335346 | 292% | 7876876 | 2280% | 7687 | 2% | 67765 | 149% |
2019-08-31 | 1001400 | 253564 | 28% | 555466 | 24% | 665677 | 8% | 56363 | 733% |
2019-09-30 | 1120000 | 233553 | 23% | 6767687 | 2669% | 42546 | 8% | 76567 | 12% |
Solved! Go to Solution.
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,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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!
In line 4 replace "Source" with "Data_Sheet"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |