cancel
Showing results for
Did you mean:
Highlighted
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
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.

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

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

4 REPLIES 4
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.

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.

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

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

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

Announcements

#### Community Highlights

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 119 members 1,642 guests
Recent signins: