exceloffice365pivot-tablepowerpivotcalculated-field

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


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.

my Data per agent:

When collapsed to show week on week

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 !


Solution

  • 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.