running total of a measure based on a RANKX

[fsfs]

hi

I have a dynamic RANKX() measure that will recalculate based on a filter context (in this case - exposition)

I have a partially dynamic [% ratio] measure that will recalculate based on date filters

it looks like below in table & slicer

what I would like to add is a running total of that percentage value,

so for row1, I would like to get 11,9%,

for row2, 16,7%

for row3, 19,8%

and for row4, 21,3% eventually

I cannot work out how to use classic cumsum approach (with FILTER(ALLSELECTED()) as AS doesn't accept table expressions as an argument and I don't want to create a calculated column with hardcoded ranking, as it will disable the option to dynamically change it by year

how can I do that in a measure?

[daxer-almighty · Accepted solution]

// You have to adjust this to your model
// since you have not shown it.

[% of Total LV - RT] =
IF( ISINSCOPE( TickerDim[_Ticker] ),
    // The name _Ticker would suggest it's a hidden field
    // or a foreign key. Such fields should not be exposed
    // and put in visuals.
    var AllVisibleTickers = ALLSELECTED( TickerDim[_Ticker] )
    var CurrentTickerRank = [rankx]
    var PercOfLv =
        SUMX(
            filter(
                AllVisibleTickers,
                [rankx] <= CurrentTickerRank
            ),
            // Would it not be nicer if you shortened
            // the name of the measure to something like
            // [% Total LV (all cat's)]?
            [% of Total Liqudation Value (all categories)]
        )
    RETURN 
        PercOfLv
)

Here's another formulation that should work. The one above might not because you're internally using ALLSELECTED in some of the functions.

// You have to adjust this to your model
// since you have not shown it.

[% of Total LV - RT] =
IF( ISINSCOPE( TickerDim[_Ticker] ),
    // The name _Ticker would suggest it's a hidden field
    // or a foreign key. Such fields should not be exposed
    // and put in visuals.
    var AllVisibleTickers = ALLSELECTED( TickerDim[_Ticker] )
    var CurrentTickerRank = [rankx]
    var PercOfLv =
        SUMX(
            AllVisibleTickers,
            [% of Total Liqudation Value (all categories)]
                * ( [rankx] <= CurrentTickerRank )
        )
    RETURN 
        IF( PercOfLv, PercOfLv )
)

[fsfs]

thank you to both of you @daxer-almighty  and  @PaulDBrown , your solutions worked

could you tell me what's the thing with ISINSCOPE() ? The bare CALCULATE gives the exact same results

so it could be broken down to

VAR with current rank of an element

RETURN with CALCULATE returning the whole measure filtered by the rankx measure <= current rank VAR

what's the point of ALLSELECTED and ISINSCOPE?

[daxer-almighty]

ISINSCOPE

ALLSELECTED

[PaulDBrown · Accepted solution]

The easiest way is:

Create a measure computing the total of the relevant column you are using the cumulative total. (Not the % column; the actual value)

Then simply divide the running total by the total:

here is an example from this thread which is seeking the same solution

https://community.powerbi.com/t5/Desktop/Percentage-of-Running-Total-for-a-Matrix/m-p/2040490#M76444... 

 

[daxer-almighty · Accepted solution]

// You have to adjust this to your model
// since you have not shown it.

[% of Total LV - RT] =
IF( ISINSCOPE( TickerDim[_Ticker] ),
    // The name _Ticker would suggest it's a hidden field
    // or a foreign key. Such fields should not be exposed
    // and put in visuals.
    var AllVisibleTickers = ALLSELECTED( TickerDim[_Ticker] )
    var CurrentTickerRank = [rankx]
    var PercOfLv =
        SUMX(
            filter(
                AllVisibleTickers,
                [rankx] <= CurrentTickerRank
            ),
            // Would it not be nicer if you shortened
            // the name of the measure to something like
            // [% Total LV (all cat's)]?
            [% of Total Liqudation Value (all categories)]
        )
    RETURN 
        PercOfLv
)

Here's another formulation that should work. The one above might not because you're internally using ALLSELECTED in some of the functions.

// You have to adjust this to your model
// since you have not shown it.

[% of Total LV - RT] =
IF( ISINSCOPE( TickerDim[_Ticker] ),
    // The name _Ticker would suggest it's a hidden field
    // or a foreign key. Such fields should not be exposed
    // and put in visuals.
    var AllVisibleTickers = ALLSELECTED( TickerDim[_Ticker] )
    var CurrentTickerRank = [rankx]
    var PercOfLv =
        SUMX(
            AllVisibleTickers,
            [% of Total Liqudation Value (all categories)]
                * ( [rankx] <= CurrentTickerRank )
        )
    RETURN 
        IF( PercOfLv, PercOfLv )
)