Group dynamic rank and use in a slicer

[GlynMThomas]

I have a report where I need to rank companies based on their revenue over a period of time set in a date slicer.  I then need to group those companies into the following:

Top 10

11 - 30

31 - 100

101 - 200

201 - 300

301 - 400

All Others

The groupings above should then be in a slicer so the user can select the top 10 companies etc based on the dates selected. I can create a measure that gives me the rankings, and I can create one that groups them from those rankings. The problem is you can't put measures into slicers and of course it wouldn't work anyway because the measure would aggregate to 1 row.

My sum measure: 

Sum By Partner = SUM('dwvw Money Movement Transaction'[New Business Value])

My Ranking measure:

Rank By Partner = RANKX(ALL('dwvw Advisers'[Partner Name]),[Sum By Partner])

My grouping measure:

Rank Group = IF([Rank By Partner] <= 10, "Top 10",
IF(AND([Rank By Partner] > 10, [Rank By Partner] <= 30),"11 - 30",
IF(AND([Rank By Partner] > 30, [Rank By Partner] <= 100),"31 - 100",
IF(AND([Rank By Partner] > 100, [Rank By Partner] <= 200),"101 - 200",
IF(AND([Rank By Partner] > 200, [Rank By Partner] <= 300),"201 - 300",
IF(AND([Rank By Partner] > 300, [Rank By Partner] <= 400),"301 - 400",
IF([Rank By Partner] > 400,"All Others", "Unknown")
))))))

My question is how would I create dynamic ranking that could then be grouped into the above categories and be presented to the user to filter based on the dates they select? I'm assuming a calculated table, but that seems not to work either as the data becomes static as soon as it's populated.

[Anonymous · Accepted solution]

Hi @GlynMThomas ,

Here's a link to the file i have created for your scenario (Refer Page 2 in the report):

https://1drv.ms/u/s!AikPceQOhqFEhAtqF3aCT5lgHBoQ?e=EFQAvt

I have created 3 groups on the basis of 'Total Quantity' measure.

You can use the same technique for your data.

Thanks.

[v-lid-msft · Accepted solution]

Hi @GlynMThomas ,

The measure is dynamic but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, if you want to use date filter we cannot make the calculated column dynamically. 

First we create a table using the rank group you defined

Then we can create a measure to define the rank dymanically.

Rank By Partner =
VAR t =
    SUMMARIZE (
        FILTER (
            ALL ( 'dwvw Money Movement Transaction' ),
            'dwvw Money Movement Transaction'[Date]
                IN FILTERS ( 'dwvw Money Movement Transaction'[Date] )
        ),
        'dwvw Money Movement Transaction'[Partner Name],
        "Sum", [Sum By Partner]
    )
VAR result =
    MAXX (
        FILTER (
            ADDCOLUMNS ( t, "rank", RANKX ( t, [Sum] ) ),
            [Partner Name]
                = SELECTEDVALUE ( 'dwvw Money Movement Transaction'[Partner Name] )
        ),
        [rank]
    )
VAR g =
    SWITCH (
        TRUE (),
        AND ( result > 0, result <= 10 ), "Top 10",
        AND ( result > 10, result <= 30 ), "11 - 30",
        AND ( result > 30, result <= 100 ), "31 - 100",
        AND ( result > 100, result <= 200 ), "101 - 200",
        AND ( result > 200, result <= 300 ), "201 - 300",
        AND ( result > 300, result <= 400 ), "301 - 400",
        result > 400, "All Others",
        "Unknown"
    )
RETURN
    IF ( g IN FILTERS ( 'Group'[RankGroup] ), result, BLANK () )

Rank Group = 
VAR g =
    SWITCH (
        TRUE (),
        AND ( [Rank By Partner] > 0, [Rank By Partner] <= 10 ), "Top 10",
        AND ( [Rank By Partner] > 10, [Rank By Partner] <= 30 ), "11 - 30",
        AND ( [Rank By Partner] > 30, [Rank By Partner] <= 100 ), "31 - 100",
        AND ( [Rank By Partner] > 100, [Rank By Partner] <= 200 ), "101 - 200",
        AND ( [Rank By Partner] > 200, [Rank By Partner] <= 300 ), "201 - 300",
        AND ( [Rank By Partner] > 300, [Rank By Partner] <= 400 ), "301 - 400",
        [Rank By Partner] > 400, "All Others",
        "Unknown"
    )
RETURN
    IF ( g IN FILTERS ( 'Group'[RankGroup] ), g, BLANK () )

You can use the rank group table to filter the rank by customer name and use the date slicer at the same time.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[GlynMThomas]

Thanks, this worked with a bit of tweaking on the filters to say where the grouping is not blank. I can then pull the values and I can make the partner slicer also dynamic. 🙂

[amitchandak]

At visual Level, you can add a TOP N filter based on revenue and it will respond to the changes in time from other silcer and visuals. 

[Anonymous · Accepted solution]

Hi @GlynMThomas ,

Here's a link to the file i have created for your scenario (Refer Page 2 in the report):

https://1drv.ms/u/s!AikPceQOhqFEhAtqF3aCT5lgHBoQ?e=EFQAvt

I have created 3 groups on the basis of 'Total Quantity' measure.

You can use the same technique for your data.

Thanks.