Forum Discussion
DAX - Calculating a Hit Rate
I have a requirement to calculate a hit rate = (number of times the actual value >=target)/(total number of hit/misses over weeks (max can only be 52weeks)). Currently the Hit Rate is calculated ...
luxpbi thanks for the response however this does not give correct answers. It does not account for the weeeks.
Please see below the structure of my expected result:
| Sales Org. | Category | Week | Hit Rate |
| Sales Org 1 | Category A | 41 | 0.95 |
| Sales Org 1 | Category B | 41 | 1.00 |
| Sales Org 2 | Category A | 41 | 0.8 |
| Sales Org 2 | Category B | 41 | 0.67 |
My data source is excel. I have added a UID by concatinating the Salesorg and Catergory.
Using the vlookup: =SUMIF(B:B;K2;I:I)/COUNTIF(B:B;K2)
My new data looks like:
| UID | Sales Org. | Category | Month | Week Number | Target | Recorded | Hit/miss |
| Sales Org 1Category A | Sales Org 1 | Category A | 1 | 1 | 0.90 | 0.96 | 1 |
| Sales Org 1Category A | Sales Org 1 | Category A | 1 | 2 | 0.90 | 0.97 | 1 |
| Sales Org 1Category A | Sales Org 1 | Category A | 1 | 3 | 0.90 | 0.88 | 0 |
| Sales Org 1Category A | Sales Org 1 | Category A | 1 | 4 | 0.90 | 0.99 | 1 |
| Sales Org 1Category B | Sales Org 1 | Category B | 2 | 1 | 0.95 | 1.00 | 1 |
| Sales Org 1Category B | Sales Org 1 | Category B | 2 | 2 | 0.95 | 0.94 | 0 |
| Sales Org 1Category B | Sales Org 1 | Category B | 2 | 3 | 0.95 | 0.95 | 1 |
| Sales Org 1Category B | Sales Org 1 | Category B | 2 | 4 | 0.95 | 1.00 | 1 |
| Sales Org 2Category A | Sales Org 2 | Category A | 1 | 1 | 0.90 | 0.96 | 1 |
| Sales Org 2Category A | Sales Org 2 | Category A | 1 | 2 | 0.90 | 0.97 | 1 |
| Sales Org 2Category A | Sales Org 2 | Category A | 1 | 3 | 0.90 | 0.88 | 0 |
| Sales Org 2Category A | Sales Org 2 | Category A | 1 | 4 | 0.90 | 0.99 | 1 |
| Sales Org 2Category B | Sales Org 2 | Category B | 2 | 1 | 0.95 | 1.00 | 1 |
| Sales Org 2Category B | Sales Org 2 | Category B | 2 | 2 | 0.95 | 0.94 | 0 |
| Sales Org 2Category B | Sales Org 2 | Category B | 2 | 3 | 0.95 | 0.95 | 1 |
| Sales Org 2Category B | Sales Org 2 | Category B | 2 | 4 | 0.95 | 1.00 | 1 |
Expected result:
| UID | Sales Org. | Category | Week | Hit Rate |
| Sales Org 1Category A | Sales Org 1 | Category A | 41 | 0,75 |
| Sales Org 1Category B | Sales Org 1 | Category B | 41 | 0,75 |
| Sales Org 2Category A | Sales Org 2 | Category A | 41 | 0,75 |
| Sales Org 2Category B | Sales Org 2 | Category B | 41 | 0,75 |
You can try creating measures as below:
Attempts # = COUNTROWS('Table') Week = WEEKNUM(TODAY()) Hit Rate = DIVIDE ( CALCULATE ( [Attempts #], FILTER ( SUMMARIZE ( 'Table', 'Table'[Recorded], 'Table'[Target] ), 'Table'[Recorded] >= 'Table'[Target] ) ), [Attempts #] )
