Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Running Totals Based Upon Teams In Data

Hi, I'm trying to get running totals based upon teams, so, what I mean is, how many calls a team has got where the value is a count. This needs to be a count of ID based upon a team, then the process repeated for another column for the field Fix Time Fix.

 

I've tried something like this but I can't get it work, any ideas?

 

Running Total =
CALCULATE(
Count('MyWorkList Case'[Id]),
FILTER(
'MyWorkList Case',
'MyWorkList Case'[ICT Teams]=EARLIER('MyWorkList Case'[ICT Teams])
&& 'MyWorkList Case'[Id] <= EARLIER('MyWorkList Case'[Id])
)
)
 
Once this is done, I mean, calculation of count per team for ID's and the First Time Fix, I then need to work out the percentage of the value First Time Fix (FTF) per team, divided by the value of ID per team * 100.
 
None of the values are currently measures, if I do a matrix, then all it gives me is the percentage across all calls together across all teams. Hope this makes sense? It needs to be based upon (totals calls / total FTF) * 100 per team.
Need Help

12 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    In case you need a measure try like

     

    Running Total =
    CALCULATE(
    Count('MyWorkList Case'[Id]),
    FILTER(
    allselected('MyWorkList Case'),
    'MyWorkList Case'[ICT Teams]=MAX('MyWorkList Case'[ICT Teams])
    && 'MyWorkList Case'[Id] <= MAX('MyWorkList Case'[Id])
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, this is what the data looks like, I need running totals instead of the default ones so that I can get the percentage right.

       

      As you can see from the image, when trying to get a percentage across the rows, this is what I get. If I try by columns, then it gives the following.

       

      It needs to be from 'MyWorkList Case'[First Time Fix] / 'MyWorkList Case'[ID] * 100. ID needs to be total calculated by team as well as First Time Fix. Hope it makes sense?

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , if you need % of running total try like

         

        Running Total % = Divide(CALCULATE(Count('MyWorkList Case'[Id]),allselected('MyWorkList Case')) ,
        CALCULATE(
        Count('MyWorkList Case'[Id]),
        FILTER(
        allselected('MyWorkList Case'),
        'MyWorkList Case'[ICT Teams]=MAX('MyWorkList Case'[ICT Teams])
        && 'MyWorkList Case'[Id] <= MAX('MyWorkList Case'[Id])))
        )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    It seems like you want to calculate the count number based on group, right?

    You could use the following formula:

    Measure =
CALCULATE (
    DIVIDE (
        COUNT ( 'MyWorkList Case'[Id] ),
        COUNT ( 'MyWorkList Case'[First Time Fix] )
    ),
    FILTER (
        'MyWorkList Case',
        'MyWorkList Case'[ICT Teams] = MAX ( 'MyWorkList Case'[ICT Teams] )
    )
)
    Column =
CALCULATE (
    DIVIDE (
        COUNT ( 'MyWorkList Case'[Id] ),
        COUNT ( 'MyWorkList Case'[First Time Fix] )
    ),
    ALLEXCEPT ( 'MyWorkList Case', 'MyWorkList Case'[ICT Teams] )
)
    Column 2 =
CALCULATE (
    DIVIDE (
        COUNT ( 'MyWorkList Case'[Id] ),
        COUNT ( 'MyWorkList Case'[First Time Fix] )
    ),
    FILTER (
        'MyWorkList Case',
        'MyWorkList Case'[ICT Teams] = EARLIER ( 'MyWorkList Case'[ICT Teams] )
    )
)

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous, thanks for this, but the figure I require is count of First Time Fix / Count of ID to get my percentage for all the teams. These figures need to be displayed per row per team as in the screenshot above.