Dynamic (slicer-dependent) ranking of time series

[Petepete]

Hi All, after a long search I couldn't find the right solution to my problem.

I got this (simplified) data (unpivoted time series):

"Day" is the time index (x-axis), "Value" is the timeseries (y-axis), while "Cat" are the categories (can imagine a stacked area chart, or a line chart)

My original problem is having a huge number of categories, hence need to rank a plot first x time series.

Also, I wanna rank by the sum of abs(value) along the whole time span, of every category

"Filter" represents hypotetically multiple columns that are spawned into slicers - so that ranking shall change dynamically.

I tried a measure like this:

Measure = RANKX (

SUMMARIZE ( ALL ( 'Table' ), 'Table'[Cat], "@ABS_VALUE", SUM ( 'Table'[ABS_VALUE] ) ),

[@ABS_VALUE],

SUM ( 'Table'[ABS_VALUE] ),

DESC

)

which stops working fine the moment the time index is popped into the table / linechart / stacked chart

Any ideas peeps?

[v-yalanwu-msft · Accepted solution]

Hi, @Petepete ;

I can try to modify it according to your measure, but I don't understand how you want to sort it. I remember that only sort it according to column, maybe you can make it clearer according to the picture.

Measure =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE (
        CALCULATE (
            SUM ( [ABS_VALUE] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Cat] = MAX ( [Cat] ) )
        )
    ),
    ,
    DESC,
    DENSE
)

The final output is shown below: 

Best Regards,

Community Support Team_ Yalan Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[Petepete]

That is excellent!

You got what I meant, Many many thanks a million 🙂

[Vera_33]

Hi @Petepete 

You can try ALLSELECTED, but it might not work as I don't have enough context of your data / visual (other filters)

Measure = RANKX (
SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Cat], "@ABS_VALUE", SUM ( 'Table'[ABS_VALUE] ) ),
[@ABS_VALUE],
SUM ( 'Table'[ABS_VALUE] ),
DESC
)