Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
My Ranking measure:
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.
Solved! Go to 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.
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
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
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. 🙂
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |