Cumulative total more than current value using current filters

[jankooy]

I am working on a survival analysis for a large list of assets.  I want to use the current filters to update the analysis.  I need to have a cumulative total for the assets that calculates the total number of assets that are less than the current age.  Something like this

Asset Age	Count at age	Cumulative total
0	5	5
1	10	15
2	15	30

where the cumulative total is the number of assets that are less than or equal to the current asset age.  

A quick look on the web suggests doing this to calculate the cumulative total:

Cumulative Total = COUNTROWS(FILTER(ALL(ASSET),ASSET[Calc_Age]<=MAX(ASSET[Calc_Age])))

 I don't want the "ALL" filter as I want to use the current filters that are set.  If I leave the "ALL" in the filter expression, the cumulative total works but it uses all of the assets in the list.  I would like to do something like:

Cumulative Total = COUNTROWS(FILTER(KEEPFILTERS(ASSET),ASSET[Calc_Age]<=MAX(ASSET[Calc_Age])))

Problem is this doesn't work.  The result is just the count at the specific age.  Something like this:

Asset Age	Count at age	Cumulative total
0	5	5
1	10	10
2	15	15

Is there a way to create a measure that will use the current filters in addition to the filtering by age so that the analysis can be dynamic?  

[parry2k]

I have no idea why you all will reply even if you are not sure what the ask is and are asking the same question. Just my 2 cents. Cheers!

[johnt75]

You can use 

Cumulative Total =
VAR MaxAge =
    MAX ( ASSET[Calc_Age] )
VAR Result =
    CALCULATE ( COUNTROWS ( ASSET ), ASSET[Calc_Age] <= MaxAge )
RETURN
    Result

This will keep any filters already in place but replace the filter on Calc_Age, coming from the visual.

If the calc age happens to be sorted by a different column then you would also need to remove the filters from the sort by column, e.g.

Cumulative Total =
VAR MaxAge =
    MAX ( ASSET[Calc_Age] )
VAR Result =
    CALCULATE (
        COUNTROWS ( ASSET ),
        ASSET[Calc_Age] <= MaxAge,
        REMOVEFILTERS ( ASSET[Sort by column] )
    )
RETURN
    Result

[xifeng_L]

Hi @jankooy 

As I understand it, you want to accumulate the asset quantity while keeping the other slicers effective.

To achieve this requirement, you only need to accumulate based on Asset Age. You can try the following measure:

Cumulative Total = 
VAR tempTbl = ADDCOLUMNS(ALL(ASSET[Asset Age]),"Asset Count",[Count at age])
VAR CurRowAge = MAX(ASSET[Asset Age])
RETURN
SUMX(
    FILTER(tempTbl,ASSET[Asset Age]<=CurRowAge),
    [Asset Count]
)

Since I’m not sure about your table structure, the table names and column names are based on the example table you provided. You can change them to your actual column names.

In addition, if [Count at age] is not a measure, but is obtained by directly dragging a field into the table, then you can replace it with the following expression:

Count at age = CALCULATE(COUNT(ASSET))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[cengizhanarslan · Accepted solution]

I am not exactly sure what you ask but I far as I understand this could help:

Cumulative Total =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'ASSET' ),
        'ASSET'[Calc_Age] <= MAX ( 'ASSET'[Calc_Age] )
    )
)

[jankooy]

Thank you.  This works.  Tried "KEEPFILTERS" but that did not work.  "ALLSELECTED" does.  

[parry2k]

@jankooy are you saying you need a cumulative total by each asset? Sorry, your question is not clear. Can you add more rows of the same asset and provide the expected output? The current sample data and output is hard to understand what exactly you are looking for.