Calculating Approval Percentage

[eliasfedai]

Hello,

I am brand spanking new to Power BI and I am having a hard time adding a single column to display card approval ratings by the card issuer. I simply want to display the approval rate and I would normaly take the count of approved transactions divided by the total count multiplied by 100. To the left of the approved column are the card issuing bank names (sensative information) 

Any help at all is appreciated. 

[Cmcmahan]

Sure.  For this, you can use a measure.  Select the table you want it associated with in your Fields pane, then click the New Measure button in the Modeling tab.

What measure you use depends on your data.  If you data is pre-aggregated, use the first expression, if you have the raw data and the table you shared with us is already a summary, use the 2nd.  I'm making assumptions about your column names, so you'll have to adapt it to your data model.

Approval % AggregateVersion = SELECTEDVALUE('Data'[Approved]) / ( SELECTEDVALUE('Data'[Approved])+SELECTEDVALUE('Data'[Declined]) ) * 100

Approval % RawDataVer = CALCULATE( COUNT('Data'[Status]), 'Data'[Status] = "Approved") / COUNT('Data'[Status])

[Anonymous]

Hello everyone, I have followed these reccomandations but was not able to achieve the desired result. We have a table for Credit Applications which has a column for the status (Accepted, Accepted subject conditions, Declined, etc) and Ideally I need to sum Accepted + Accepted subject conditions as a whole accepted criteria and then divide by the total (accepted + accepted subject conditions +declined) to obtain an Approval Rate like this:

This is how the table looks like and how I was trying to define a measure for the Accepted rows so I can then divide the two measures:

what do you suggest?

Thank you!

[eliasfedai]

Hi Cmcmahan,

Appreciate the quick response. So the data I was displaying was a matrix that was summarizing my raw data. I renamed my table to Data just to make it easier for me to follow along. Again I apologize I am dummy to Power Bi, the only column I have under visualizations is the "Response" one. 

My matrix originally looks like the first screenshot below. I inputed the second measure you provided and if you look at the second screenshot there are far more columns being added besides the far right one which is the goal. I've attempted to exclude the additional columns however certain ones such as Approval % RawDataVer which has a value of 1.00 but it will take the Approved Count of Response column away when that is not my intention. Any suggestions?

FirstSecond

[Anonymous]

I am a bit confused by what your raw data looks like. Is this what you are looking for?

[eliasfedai]

Hi Nickhamilton,

Appreciate your help and input. The ultimate goal is to have something very similar to what you have. 

Instead of card I want the card issuing bank on the far left column and basically exactly what you have in your screenshot.

I've included an excel screenshot of what I am trying to achieve. The blacked out part are the names of the card issuing banks. 

[Cmcmahan]

Move the Response field from the columns bucket into the Values bucket.

You're getting the left hand set of columns because one of the banks (the one with 123 approvals) has a Blank entry in the Approved/Declined column.  The rest of the columns have your data grouped up by whether it was approved or denied, and then it tries to recalculate the approval % based on just that data. 

With matrices, remember that columns and rows are groupings of your data. While items in the values bucket are the columns of data that you want to see the values for once everything is grouped properly.  The matrix visual is just a quick way to iterate through and display all those combinations.  

[eliasfedai]

The one issuing I am having when I move the Response field from the columns into the Values bucket, I lose the two columns of Approved and Declined which is required. 

Do you believe maybe I should use a different format other than matrix? 

[Cmcmahan · Accepted solution]

No, a matrix should work for this.  I was mixing up aggregated/non-aggregated solutions in my head, leading to this issue. 

Quickest way to fix it would be a pair of measures that you add to the values bucket in place of Response:

ApprovalCount = CALCULATE( COUNT(table1[Response]), table1[Response] = "Approved")

DeclinedCount = CALCULATE( COUNT(table1[Response]), table1[Response] = "Declined")

[eliasfedai]

Thanks a lot Cmcmahan. Never even consider the approach of having measures count the approved and declined. Appreciate your help on this.