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
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
Proud to be a Datanaut!
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.
Hit = SUM( Table3[Hit] )
Total attempts = COUNT( Table3[Hit] )
Hit Rate = DIVIDE( [Hits] ; [Total attempts] )
VAR Hits = SUM( Table3[Hit] )
VAR TotalAt = COUNT( Table3[Hit] )
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.
@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:
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:
You can try creating measures as below:
Attempts # = COUNTROWS('Table')
Week = WEEKNUM(TODAY())
Hit Rate =
SUMMARIZE ( 'Table', 'Table'[Recorded], 'Table'[Target] ),
'Table'[Recorded] >= 'Table'[Target]
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!