Success rate calculation

louiswatson · 2022-09-16T10:11:33+00:00

There was an error rendering this rich post.

I'm trying to work out the success rate of applications submitted, attached is a basic flow diagram of how this calculation works.

I have setup an ETL which filters out any withdrawn applications and separates the approved and refused applications.

Is there a way that I can sum the values of Approved Applications and Approved/Refused applications? I could then do a divide calculation to get the success rate.

Thankyou in advance!

Find more posts tagged with

Magic ETL

Accepted answers

All comments

GrantSmith

You could utilize a formula tile or a beast mode (if you want to apply filtering dynamically to your calculation) to calculate it:

`Approved` / (`Approved` + `Refused`)

RobSomers

@louiswatson As you have it set up now, if you haven't already, you'll need to add a column to each branch named something like 'Type' that identifies 'Refused' for the Refused branch and 'Approved' for the Approved branch and then append the two. Then do the following:

SUM(case when 'Type' = 'Approved' then 1 else 0 end)/SUM(1)

Also, if you're able to, I would suggest creating the 'Type' tag in a formula tile and then you don't need to have the split and then append. It'll simplify things and do it in just one step instead of multiple.

louiswatson

Thankyou both, @RobSomers I tried the formula but the value didn't come back as expected. I have used a Group By after adding the 'Type' column.

What would be the best way to work out Approved/Approved and Refused?

Many thanks

RobSomers

@louiswatson You would use the formula I gave you as a beast mode in a card if the data was at an application grain. You can use a Rank & Window tile to get the totals after the Group By tile and join it to your groups and do the calculation from there, or you can use the FIXED function in a formula tile to do the calculation all in one go.

https://domohelp.domo.com/hc/en-us/articles/4408174643607-Beast-Mode-FIXED-Functions

louiswatson

@RobSomers if I already have the totals using Group By, do I need to use rank & window too?

I had a go with the FIXED function but wasn't sure if this was being used in the right way?

Now I have the totals I just need to get my head around the calculation, it should work out to be 1017 (total applications) / Approved (612) = %

louiswatson

Attached is the error that I had with the FIXED function, this calculation would have worked out the total applications submitted. I would need to filter out other, then work out 'approved' and 'approved and refused'.

RobSomers

@louiswatson If you do the beast mode for each percentage in a card, this becomes a lot simpler because you can do:

SUM(case when 'Type' = 'Approved' then 'Approved or Refused' end)/SUM('Type')

And do the same for Refused and Other. If you want to do it in the ETL and do a separate column for each success rate, then you can use the formula above to do the same thing. You only need to use the FIXED function if you want to do the calculation with a single column in the ETL.

If you want to go the FIXED route, in your formula, you don't need anything in the parentheses after fixed because you're interested in the sum of the whole dataset. So your formula would look something like this:

SUM('Approved or Refused')/SUM(SUM('Approved or Refused') FIXED())

If you want to ignore 'Other' in your totals, you can use a CASE WHEN statement in the SUM portion of your FIXED function to only count 'Approved' and 'Refused'.