Running total per age group (no date-column)

[whulsbergen]

What am I doing wrong?

I need to find the average age group that people die of COVID-19. So I thought first to make a calculated column of Running Total deaths per age category. And then find the value that is just over half of the total number of deaths and produce the corresponding age-group.

The first step already doesn't work for me. I tried all the community posts I could find but no answer.

Calculated column

Table:

As you can see the table just shows the total number of deaths on each row.

I tried the formula without "FILTER", but that returns an error:

Please help!

[v-diye-msft]

Hi @whulsbergen 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[amitchandak]

@whulsbergen , Your formula is correct, but that is measure not column.

[whulsbergen]

ok. In other posts it looked like the measure and column-solutions were almost identical, and columns I can see quicker what it looks like, but let me try measure instead..

Well, it worked! 

opgeteld overleden =

VAR RT =

CALCULATE(

SUM('Confirmed COVID-19 cases by sex and age'[Deaths]);

FILTER(

ALL('Confirmed COVID-19 cases by sex and age');

'Confirmed COVID-19 cases by sex and age'[Index] <= max ( 'Confirmed COVID-19 cases by sex and age'[Index] )

))

VAR TOTALD = CALCULATE(sum('Confirmed COVID-19 cases by sex and age'[Deaths]);ALLSELECTED())

RETURN

DIVIDE(RT;TOTALD;0)

Next step: finding the 50% category (80-84y)

And I still don't know what went wrong with the column. But I'm out of the woods for now!

[amitchandak]

Try like

opgeteld overleden =

RETURN
DIVIDE(CALCULATE(
SUM('Confirmed COVID-19 cases by sex and age'[Deaths]);
FILTER(
ALL('Confirmed COVID-19 cases by sex and age');
'Confirmed COVID-19 cases by sex and age'[Index] <= max ( 'Confirmed COVID-19 cases by sex and age'[Index] )
));CALCULATE(sum('Confirmed COVID-19 cases by sex and age'[Deaths]);ALL('Confirmed COVID-19 cases by sex and age'));0)

[miltenburger]

Hi @whulsbergen 

Can you try following:

Cumulative Quantity =
CALCULATE (
    SUM ( 'Confirmed COVID-19 cases by sex and age'[Deaths] ),
    FILTER (
        ALL ( 'Confirmed COVID-19 cases by sex and age' ),
        'Confirmed COVID-19 cases by sex and age' [Index] <= MAX ( 'Confirmed COVID-19 cases by sex and age' [Index] )
    )
)

[whulsbergen]

Tried. Shows something different now. No Totals running yet 😞