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 ...
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 #]
)