Running Totals Based Upon Teams In Data

[Anonymous]

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.

[Anonymous]

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]

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.

[amitchandak]

@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]

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?

[AllisonKennedy]

@Anonymous If I am understanding you, you are looking for percent completed first time, not running total. 

You cannot do this with the show values as. You will need to create a new measure: 

Percent First Time = DIVIDE( COUNT( 'MyWorkList Case'[First Time Fix]) , COUNT( 'MyWorkList Case'[ID] ) )

Format it as a percent (You don't need to multiply by 100). 

Then add this to the matrix visual in your screenshot. 

[Anonymous]

Hi @kallie, thanks for this, but when I add it as an additional column, I get ann error with my visual.

[amitchandak]

@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]

Sorry, is this per team - First Time Fix / ID * 100?

Its cause its not giving me that, or am I doing something wrong?

[amitchandak]

@Anonymous ,

Divide(Count(Table[First Time Fix]), Count(Table[ID])) *100

// *100 Is not needed you can mark it as % from measure tools

if this did not work then

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

[Anonymous]

Here's link to test copy of Power BI file without sensitive data.

Power BI File 

[Anonymous]

Hi @Anonymous ,

Sorry for that I have trouble getting your pbix file.

Please provide me with more details about your table and your problem. 

Best Regards,Eyelyn Qin

[Anonymous]

Hello @amitchandak, sorry, only started to use this forumn just before the Christmas break, can you tell me how I can upload some samle data for you (and others) to help me out with this?