Getting the Average from a Y/N column

[mrizvi]

Hello Power Bi community

This is a sample of my data table 

Site Name	Date	Status	Meets Criteria
Nashville	11/10/2020	Verified	Y
N. Dallas	09/22/2020		N
Danville	2/5/2020		N
Danville	6/6/2020	Verified CVS Pharm	Y

I am trying to use the Meets Criteria column to get a monthly average of each site's orders. If the site has a "Y" in the last column that means they met the criteria and vise versa for "N". I need to determine how to create a measure (or a column) to build a matrix visual scorecard showing each site's monthly average of the number of orders that were verified and met the criteria (i.e. "Y")

Thanks,

M.R

[V-pazhen-msft]

@mrizvi 

Could you clarify your requirement, you want the monthly average count, just count the monthly order that meet the 2 conditions? And in the Status column, are there any other status other than Verified, and Verified CVS Pharm?

Regards
Paul

[mrizvi]

There are a lot more status. Most all will have verified. If the order was not verified... The row is blank. 

[AllisonKennedy]

@mrizvi  Do you also have an OrderNumber or ID in this table? I would guess measure would look something like:

Total Orders Verified and Meet = CALCULATE( COUNT( Table[OrderId]) , Table[Status] = "Verified", Table[ Meets Criteria] = "Y" )

I'm not sure what you want to calculate the average over - see if this post helps explain why that's important: https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html

So if you can provide more context on how AVERAGE is calculated, that would help us help you.

[mrizvi]

@AllisonKennedy I do have the Order ID as one of the columns in the same table.

I need to know which sites had 85% verified orders per month. 

[AllisonKennedy · Accepted solution]

@mrizvi  OK, so you are looking for percentage by Site Name. Try these measures: 

Total Orders Verified = CALCULATE( COUNT( Table[OrderId]) , Table[Status] = "Verified", Table[ Meets Criteria] = "Y" )

Total Orders = COUNT( Table[OrderID]) 

Percent Verified = DIVIDE( [Total Orders Verified] , [Total Orders] )

Use a slicer for Site Name or put Percent Verified in a visual with Site Name. If you want this to work when drilling down to order ID level, you'll need to add another filter context shift, for example using: 

Percent Verified This Site = CALCULATE( DIVIDE( [Total Orders Verified] , [Total Orders] ), ALLEXCEPT(Table[Site Name])