Calculation on Measure Totals

[egsiegel]

I have a measure that calulates the number of minutes "worked" by an call center agent.  It takes the number of phone calls * the expected duration of a phone call and the number of emails handled * the number of minutes that an email should take to handle to calculate the time "worked".   There is a data row for each day, for each agent, for the period being dispayed.

I also have, in another table, the number of minutes that the agent was logged in for work.

so it looks something like this:

agentName	skill	Count	Date
Fred	Call	200	2/1/2021
Sue	Call	95	2/1/2021
Liz	Call	130	2/1/2021
Fred	Email	180	2/2/2021
Sue	Email	135	2/2/2021
Liz	Email	130	2/1/2021

My measure then takes this data an multplies Calls*5 and Emails*8 to get the workload (minutes) for each agent.

this is shown in a Matrix Visualization.

I now need to take the totals on that Matrix and find the percentage of time logged in (below in seconds) that the agent actually worked.   So I need to devide my measure total (multiple Days possibly) by the appropriate agents logged in time total (same date range).   

AgentName	Logintime	Date
Fred	20000	2/1/2021
Sue	25000	2/1/2021
Liz	22000	2/1/2021
Fred	22000	2/2/2021
Sue	22000	2/2/2021
Liz	22000	2/2/2021

I'm struggling with calculating the agent percetage based on the total work.

Thanks in advance for your help.

[egsiegel]

thank you...that looks like what I am looking for....I will give it a shot

[v-robertq-msft · Accepted solution]

Hi, @egsiegel 

According to your description and example, I can understand roughly your logic and the output you want to get, you can try this measure to replace the calculated column I created before to calculate the percent:

To test the function of this measure, I changed the sample data to make them within the same date, like this:

agent percetage1 =

var _valueofCall=

CALCULATE(SUM(Merge1[Count]),FILTER(ALL(Merge1),[skill]="Call"&&[AgentName]=MAX('Merge1'[AgentName])))

var _valueofEmail=

CALCULATE(SUM(Merge1[Count]),FILTER(ALL(Merge1),[skill]="Email"&&[AgentName]=MAX('Merge1'[AgentName])))

var _valueoflogintime=

CALCULATE(SUM(Merge1[Table (2).Logintime]),FILTER(ALL(Merge1),[AgentName]=MAX('Merge1'[AgentName])))

return

DIVIDE(_valueofCall*5+_valueofEmail*8,_valueoflogintime)

Then you can create a table chart and place it like this:

And I guess this can be what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @egsiegel 

According to your description and sample data, I can not understand your requirement clearly, but I guess the logic of your [agent percentage] can be [Count]* expected duration / [Logintime], am I right or I misunderstood?

If I’m right, you can try my steps:

1. I go to the Power Query editor to merge the two tables to a new table ‘Append1’, here are my steps:

2. Then I created a calculated column:

agent percetage =

var _value=

SWITCH(

    [skill],

    "Call",[Count]*5,

    "Email",[Count]*8)

return

DIVIDE(_value,[Table (2).Logintime])

3. I created a table chart and place like this:

And I guess this can be what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[egsiegel]

thank you for your resonse...

your understanding of what I am looking for is correct....unfortunately, you result has the same issue I am running into.  

In the Data, Fred is logged in for 20000 seconds on 2/1/2021.

Your result carries that 20000 into the merged table for both Email and Calls.

Your percentages show the percent of 20000 spent on Emails and the percent of 20000 spent on calls...it really calculates based on 40000 seconds of login time.

what I am trying to get to is ((Calls*5)+(Emails*8))/login_time)

this needs to be true for a single day as well as a date range (sum of calls)*5+(sum of emails * 8)/(sum of loginTime)  with logintime once for each day.

I hope this helps with the explanation of the issue.

and again...thank you in advance for your help