Forum Discussion
DAX - Calculating a Hit Rate
Hi Anonymous,
Is your data structure as you show in the image, have you done changes manually ?
If so, what is the native structure of your data?
If you don't, you can solve it using 3 simple measures or combine them into one.
Measure 1:
Hit = SUM( Table3[Hit] )
Measure 2:
Total attempts = COUNT( Table3[Hit] )
Measure 3:
Hit Rate = DIVIDE( [Hits] ; [Total attempts] )
Combined :
Combined = VAR Hits = SUM( Table3[Hit] ) VAR TotalAt = COUNT( Table3[Hit] ) RETURN DIVIDE( Hits ; TotalAt )
Then you just need to put a slicer filter with the weeks. You can use a simple filter to see only one week in particular or you can chosse the less than or equal to to see the accumulate.
Hope that helps.
Regards,
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 #] )
