cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lenon Frequent Visitor
Frequent Visitor

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 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 MonthWeek NumberTargetRecordedHit/miss
Sales Org 1Category A110.900.961
Sales Org 1Category A120.900.971
Sales Org 1Category A130.900.880
Sales Org 1Category A140.900.991
Sales Org 1Category B210.951.001
Sales Org 1Category B220.950.940
Sales Org 1Category B230.950.951
Sales Org 1Category B240.951.001
Sales Org 2Category A110.900.961
Sales Org 2Category A120.900.971
Sales Org 2Category A130.900.880
Sales Org 2Category A140.900.991
Sales Org 2Category B210.951.001
Sales Org 2Category B220.950.940
Sales Org 2Category B230.950.951
Sales Org 2Category B240.951.001

 

5 REPLIES 5
Super User IV
Super User IV

Re: DAX - Calculating a Hit Rate

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

luxpbi Member
Member

Re: DAX - Calculating a Hit Rate

Hi @lenon,

 

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,

 

lenon Frequent Visitor
Frequent Visitor

Re: DAX - Calculating a Hit Rate

@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.CategoryWeek Hit Rate
Sales Org 1Category A410.95
Sales Org 1Category B411.00
Sales Org 2Category A410.8
Sales Org 2Category B410.67
lenon Frequent Visitor
Frequent Visitor

Re: DAX - Calculating a Hit Rate

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:

 

UIDSales Org.Category MonthWeek NumberTargetRecordedHit/miss
Sales Org 1Category ASales Org 1Category A110.900.961
Sales Org 1Category ASales Org 1Category A120.900.971
Sales Org 1Category ASales Org 1Category A130.900.880
Sales Org 1Category ASales Org 1Category A140.900.991
Sales Org 1Category BSales Org 1Category B210.951.001
Sales Org 1Category BSales Org 1Category B220.950.940
Sales Org 1Category BSales Org 1Category B230.950.951
Sales Org 1Category BSales Org 1Category B240.951.001
Sales Org 2Category ASales Org 2Category A110.900.961
Sales Org 2Category ASales Org 2Category A120.900.971
Sales Org 2Category ASales Org 2Category A130.900.880
Sales Org 2Category ASales Org 2Category A140.900.991
Sales Org 2Category BSales Org 2Category B210.951.001
Sales Org 2Category BSales Org 2Category B220.950.940
Sales Org 2Category BSales Org 2Category B230.950.951
Sales Org 2Category BSales Org 2Category B240.951.001

 

Expected result:

UIDSales Org.CategoryWeek Hit Rate
Sales Org 1Category ASales Org 1Category A410,75
Sales Org 1Category BSales Org 1Category B410,75
Sales Org 2Category ASales Org 2Category A410,75
Sales Org 2Category BSales Org 2Category B410,75
AkhilAshok Established Member
Established Member

Re: DAX - Calculating a Hit Rate

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors