Trying to get the Average of the count of items

[Thomas_MedOne]

We have a table. it has many values in but some of the values are these:  DCP-Data

Service Date	CODE	Shift
1/2/2023	99154	Afternoon
1/2/2023	99154	Evening
1/3/2023	99154	Afternoon
1/3/2023	99154	Afternoon
1/2/2023	99154	Evening
1/2/2023	99202	Afternoon

What we need to know is the average number of times this particular code has been used over a period of time. We may filter by shift but we may also filter by date or pay period (a month at a time)

I created this measure based on some research: 

AvgCode = AVERAGEX(VALUES('DCP-Data'[SERVICE DATE]), calculate(count('DCP-Data'[CODE])))

What ended up happening, though, is for the given period we looked at (a month in this case) it only selected the values in the month and averaged the times it was used only.  So, above example, 99154 was used on two days five times that was 2.5 times on average. It ignored the line with 99202 in it.   What I really need is out of every day in that month (1/1 - 1/30) how many times on average was that used? In this example it would be < 1 time.  

If I'm slicing on the shift name, then it wold adjust accordingly to tell me how many times in the month just for that shift.

I can't figure out how to do that. I tried using just the table and not "SERVICE DATE" in the first values but that messed things up even more.

Can anyone think of what can be done?

[some_bih]

Hi @Thomas_MedOne It is possible that some of scenarios below fits your need.

Measure below, Avg, calculate average per single Date. You need to create Calendar / Date table if yo do not already have, and make relationships.

If you need avg per Code then right upper table is shown, other view are different possible options as I do not get your request.

Avg =

AVERAGEX(

VALUES('Date'[Date]),

    CALCULATE(COUNTA('Sheet1'[CODE]))

)

Did I answer your question? Kudos appreciated / accept solution!

[Thomas_MedOne]

Unfortunately this is not the average we want. This is essentially what I have now but instead of Count() you have counta().  It is hard without giving you actual data. I think me giving you this data is not quite the whole picture. Let me see if I can explain.

We are a 24/7 operation and most shifts happen every day. So, if we're considering average times a code is used per shift it should take into account every instance of that shift. So, if there are 40 shifts in a month that are the same and it was only used 5 times, then that's 5/40 to get our average, right?  But what your measure (and mine) are doing is only taking the days in which it was used. So, say those five times were done on two shifts then it's diving the number of times over 2 shifts and not 40.

In a real world example. In our table for the month of July. The 99202 code was used on the following dates:
SERVICE DATE
7/10/2023
6/23/2023
6/27/2023
7/6/2023
7/13/2023
7/11/2023
7/10/2023
7/13/2023
7/13/2023
7/6/2023

the measure is giving a value of 1.33 for the average. But it is not taking into account all the other days and/or shifts that happened that month.

I know this is very complicated. Perhaps it can't be done.

[some_bih]

Hi @Thomas_MedOne if you have data you can figure out how to "replace" your actual data, like instead of name John put J or something.

As you know logic and what is expected then "everything is possible". It is very hard to understand your request without "actual" data.