Forum Discussion

jankooy's avatar
jankooy
New Member
5 months ago
Solved

Cumulative total more than current value using current filters

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 AgeCount at ageCumulative total
055
11015
21530

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 AgeCount at ageCumulative total
055
11010
21515
 
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?  
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

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

     

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 months ago

    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.

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    4 months ago

    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's avatar
      jankooy
      New Member
      4 months ago

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

  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    4 months ago

    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~

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 months ago

    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
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 months ago

    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!