How to get 'Sum' working in Calculated field based on another calculation?

Question

I have a set of data. I need to know if the agent has gone over 25.9 seconds in ACW, if so produce a 1 in the cell, then i want the total number of agents who have bene over this target to be displayed in the subtotal.

So far I have managed to achieve this when its broken down per agent, but my overall either per TM, per month or per week is showing at 0.

as you can see the values are 0. When based on the data it should show 6 for week one, as 6 agents did not hit under 26 seconds in week 1.

I read online that this is due to it working it out based on the row and not what is sat under that. So for example the week 1 overall ACW is 17, so this does go over the 25 seconds, thus giving a 0.

What i want is per agent how many of them went over the 25 seconds in that time period either by month or by day depending on what i choose to then display this total number of agents for the TM or per month or per week.

I have played about with calculated fields and loading the data into power pivot but im not getting what I need.

In power pivot I have used this calculation which works per agent but not per TM or per Week or Per Month.

OVER 25 Seconds:=IF([IB_ACW]>25.9,1,0)

Could someone please advise me what the correct way to get this to work is?

Thank you in advance! Happy Friday !

Answer 1

Update your IB_ACW measure to use DIVIDE instead of IFERROR and / :

IB_ACW:=
  DIVIDE(
    SUM(DATASHEET[IB_WRAP]),
    SUM(DATASHEET[IB_CALLS]),
    0
  )

You will need to use SUMX like:

OVER 25 Seconds:=
  SUMX(
    DISTINCT('YourTable'[Agent]),
    IF([IB_ACW] > 25.9, 1, 0)
  )

Update the above to the relevant table & column name for Agent.