Call Centre Daily Occupancy

[MrPatrick]

I have a small call centre with 24 seats. I would like to calculate in DAX(?) the daily occupancy as a percentage of the total available seats.

That is of course straight forward for a single day, I have the following measure that works for a single day.

Occupancy % = DISTINCTCOUNTNOBLANK('vicidial_agent_log'[user])/24*100

If I select greater than a day of course it adds all the days into a single value and I can end up with >100% values which obviously

doesn't make sense.

The agent_log table has two columns:

User | callDate

user1 | 03 Jan 2019

user2 | 03 Jan 2019

user3 | 03 Jan 2019

user1 | 04 Jan 2019

user3 | 04 Jan 2019

user2 | 05 Jan 2019

user3 | 05 Jan 2019

user4 | 05 Jan 2019

user2 | 06 Jan 2019

user5 | 06 Jan 2019

So what I need to do is calculate the number of unique users logged in on each day rather than simply across the whole dataset.

My first thought was that this should be possible in DAX by wrapping the current expression in CALCULATE and applying a filter expression for the callDate column, and whilst I got close I couldn't get to work.

In fact I'm sure I'm close I'm just struggling to get my head round the filter part of the expression:

Occupancy % =

CALCULATE (

DISTINCTCOUNT ( vicidial_agent_log[user] ),

FILTER (

????

)

)/24*100

EDIT:

Call Centre Daily Occupancy.pbix 

Attached is a copy of the file posted by v-kelly-msft, i've updated it with a sanitised version of my actual data and the various solutions posted here.

Everybodies solution works on a per day basis but they all end up with an overall total of 254% for the period, which is obviously incorrect.

So I suppose the second part of my quesion is:

• how do I also get this to average the values properly to give a total for the whole period selected in the filter.

For the record the average occupancy across the whole data set is ~76%, the day by day breakdown calcualted directly from the database is included in the table CalculatedOccupancy

[littlemojopuppy]

Is this your expected result?

Two measures:

• Users Logged In:=DISTINCTCOUNT('Sample Data'[User])
• Occupancy %:=DIVIDE([Users Logged In], 24, BLANK())

The filter context will take care of filtering for the appropriate date for you.

[v-kelly-msft]

Hi @MrPatrick ,

You need to ceate a measure as below:

Occupy% = CALCULATE(DISTINCTCOUNT('Table'[User])/24,ALLEXCEPT('Table','Table'[ callDate]))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,

Kelly

[MrPatrick]

Thanks @v-kelly-msft, I appreciate the time you've spent helping with this. Unfortunately on my live data your measure works for each day but doesn't give a correct figure when the filter is for more than one day. If I apply this to my whole dataset I get a total occupancy of 254%, which is correct in that we see a total of 61 unique users over the ~year period (and 61/24 = 2.54), but is incorrect as I need to calculate the total as an average of the daily %.

I realise that my initial question was too limited for what I am trying to achieve.

I hope that makes sense, I've tried to modify what you've posted but I haven't been able to get it to work. The closest I got was based on another post on this forum, I got

Occupancy % =

CALCULATE (

DISTINCTCOUNT ( agent_log_sanitised[user] ),

FILTER (

ALL ( agent_log_sanitised ),

MAX ( agent_log_sanitised[callDate] ) = [callDate]

)

)/24*100

I played around with a MIN value in the filter but it didn't seem to affect anything 'MIN ( agent_log_sanitised[callDate] ) = [callDate]'

My other thought was to create a new table in the model and calculate users per call day and then reference that in a DAX query but this seems overly complicated?

I've attached to my original post an updated copy of the PBIX you kindly provided with a version of my actual data and several attempts at getting the occupancy data to work. If you can spare a moment to have another look I would be very grateful.

[v-kelly-msft]

Hi @MrPatrick ,

After checking your .pbix file,I'm a little confused about what you're expecting for?As in the year of 2019,there're 61 users in your table,so if you wanna calculate a summarized data of the whole year,it should be 61/24*100,and the result must be over 100%.

So I'm wondering if you wanna get a result as below? The measure marked in red?

  

If so,you only need to use an average function to calculate in

'CalculatedOccupancyData'[Column1]

Measure = AVERAGE('CalculatedOccupancyData'[Column1]) 

Hope this would help.

Best Regards,

Kelly

[amitchandak]

Try Like

Total = Calculate(
    AvergaeX(   
            SUMMARIZE(
                vicidial_agent_log,
                vicidial_agent_log[callDate],
				"_agents",
				DISTINCTCOUNTNOBLANK('vicidial_agent_log'[user]),
                "_dates"
				DISTINCTCOUNTNOBLANK('vicidial_agent_log'[callDate]),
				"_comp"
            ),
            (divide([_agents],[_dates]*24) )*100
)
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MrPatrick]

Thanks for your reply, this doesn't work however, it presents an error "Column '_comp' added by the SUMMARIZE function must have the scalar expression specified"