Forum Discussion
Getting the Average from a Y/N column
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
Anonymous 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])
5 Replies
- AllisonKennedy
Community Champion
Anonymous 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.
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
Anonymous 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])