The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
@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])
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Anonymous
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
There are a lot more status. Most all will have verified. If the order was not verified... The row is blank.
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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.
@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])
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
261 | |
120 | |
113 | |
83 | |
71 |