I’ve 3 tables, 1st table gives me all the disbursed cases count and the amount, 2nd table gives me all the case staus that are presented and either get bounced or cleared, 3rd tables is date table.
1st table has all the unique ids. The 2nd table has duplicate ids, as month on month data is available for each id that gets represented till the loan tenure ends.
I want to calculate the count of cases disbursed in particular month;
Disbursal Count# = Count(loan_id)
Bounce# = Calculate (distinctcount(loan_id) , Filter (2nd table (status) =“Bounce” ))
I’m getting the correct result by date, product, program_type any filter I use for 1st measure, but I want to divide (Bounce# / Disbursal Count#). And want this to work if the user applies any filter on month, product or location.
2nd table has a date column but for all the id’s the date would be either 2nd or 7th of that month.
Test.xlsx (27.8 KB)
I’ve uploaded a sample data for the reference.