cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GlynMThomas Frequent Visitor
Frequent Visitor

Group dynamic rank and use in a slicer

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
vaibhavdesai Established Member
Established Member

Re: Group dynamic rank and use in a slicer

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 New Contributor
New Contributor

Re: Group dynamic rank and use in a slicer

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

 

1.PNG

 

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.

 

2.PNG3.PNG

 

 

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

4 REPLIES 4
vaibhavdesai Established Member
Established Member

Re: Group dynamic rank and use in a slicer

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.

 

amitchandak New Contributor
New Contributor

Re: Group dynamic rank and use in a slicer

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. 

v-lid-msft New Contributor
New Contributor

Re: Group dynamic rank and use in a slicer

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

 

1.PNG

 

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.

 

2.PNG3.PNG

 

 

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 Frequent Visitor
Frequent Visitor

Re: Group dynamic rank and use in a slicer

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. :-)

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 119 members 1,642 guests
Please welcome our newest community members: