DAX - Calculating a Hit Rate

[Anonymous]

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 at Sales Org level in excel. However the data structure has changed(Please see the screenshot attached for the sample data structure).

I want to be able to see the hit-rate at Sales Org level or for a Specific category for the current week.

Example. Currently we are in week 40 of the year. We have been able to hit the traget 36 times(weeks) at Sales Org level then.. Hit Rate =36/40.

How can this be realised in Power BI for both Sales Org and Category. The data structure can be changed to suit the solution. Thanks in advance

Sales Org.	Category	Month	Week Number	Target	Recorded	Hit/miss
Sales Org 1	Category A	1	1	0.90	0.96	1
Sales Org 1	Category A	1	2	0.90	0.97	1
Sales Org 1	Category A	1	3	0.90	0.88	0
Sales Org 1	Category A	1	4	0.90	0.99	1
Sales Org 1	Category B	2	1	0.95	1.00	1
Sales Org 1	Category B	2	2	0.95	0.94	0
Sales Org 1	Category B	2	3	0.95	0.95	1
Sales Org 1	Category B	2	4	0.95	1.00	1
Sales Org 2	Category A	1	1	0.90	0.96	1
Sales Org 2	Category A	1	2	0.90	0.97	1
Sales Org 2	Category A	1	3	0.90	0.88	0
Sales Org 2	Category A	1	4	0.90	0.99	1
Sales Org 2	Category B	2	1	0.95	1.00	1
Sales Org 2	Category B	2	2	0.95	0.94	0
Sales Org 2	Category B	2	3	0.95	0.95	1
Sales Org 2	Category B	2	4	0.95	1.00	1

[luxpbi]

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,

[Anonymous]

@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

[Anonymous]

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

[AkhilAshok]

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

[Greg_Deckler]

This seems straight-forward. Can you post that data in text format? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490