Computing Percentage Test Results (String) for a Certain Application (Category)

[Anonymous]

Hello,

I am looking to create a Table where I have mulitple different web apps that I support that contains a column that shows the results of a security scan as a string value of Pass or Fail. These tests run multiple times a month many times for the same app in one month, so my data may look like this: 

Application Name	Date Performed	Test Results
Database App	1/16/2021	Pass
Apple App	1/17/2021	Fail
Database App	1/18/2021	Pass
Apple App	2/20/2021	Pass
CRM App	2/25/2021	Fail

In PowerBi, I am trying to find the pass rate (%) of each of the apps.

So I want to calculate for each distinct app: COUNT(Pass) / (COUNT("Pass")+Count("Fail")) to give me a percentage. 

What I did was create 2 calulated columns:

1.  Pass Column - 

IF(Table1[Test Result]="Pass",1,0)

-- If Test Result = pass, then put 1 in column, else 0

2. Pass Rate Calculation - 

SUM(Table1[Pass Column])/(COUNT(Table1[Pass Column]))

Explained: Sum of total passes divided by the count of tests (total tests)

The part that I am stuck on is that I need this calculation to calculate the percentage based on the Application Name. 

Is there a way to specify the Application Name (Category) for the calculation? 

I need my results to look like this:

Application Name	Pass Rate (%)
Database App	85%
CRM App	91%
Apple App	75%

I apologize if this is confusing, I'm just starting to learn PowerBi.

[v-janeyg-msft]

Hi, @Anonymous 

Is your problem solevd? If not, pls let me know.

From your original description, I found that your needs are very simple, but your description and practices are confusing. How are these results calculated? We only need to know your complete calculation logic(pure text description). 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[parry2k]

@Anonymous I'm referring to sample data you posted in your original post, it is 3/5, no? But you are not replying to what % you are expecting based on your original sample data?

Application Name	Date Performed	Test Results
Database App	1/16/2021	Pass
Apple App	1/17/2021	Fail
Database App	1/18/2021	Pass
Apple App	2/20/2021	Pass
CRM App	2/25/2021	Fail

[parry2k]

@Anonymous not sure based on the sample data how you are getting 66%, the way you are calculating it should 3/5 = 60% ( sum of 3 pass rows and total rows = 5 ) how you are showing 2/3?

Also based on your data, how you expected your final test results to be the % you are showing? You have to be very clear about what you are looking for specifically when you are showing sample data the make sure your expected output is based on the sample data.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thank you for the resource link. 

I am getting 66% from my example reply to you. In that example, this would be the percentage for 1 application category

	Pass/Fail	1 if Pass, 0 if fail
	Pass	1
	Fail	0
	Pass	1

So in this example, I would have 2 passes and 3 total rows. So the calculation would be 2/3 (66%)

[AlexisOlson]

If you create a table or matrix visual and you put Application Name and your Pass Rate measure in it, what are you currently getting?

[Anonymous]

I'm getting very high percentages, something like 1600% for one of the values, all over 100%

[parry2k]

@Anonymous you are complicating the calculations, how you are getting to that % you are looking for.

[Anonymous]

In the first column, I am turning the String Value Pass into a number (1) and if the string value does not equal Pass, I am putting 0 in the row value. 

Then I am taking a SUM of the column, so it is adding up all the 1's. 

Then I am dividing that sum by the count of total rows in the column:

Pass/Fail	1 if Pass, 0 if fail
Pass	1
Fail	0
Pass	1

So in this example, I would have 2 passes and 3 total rows. So the calculation would be 2/3 (66%)