Dynamic table creation

[ww1711]

Hello,

I have a requirement to show the top-5 values within a dataset based on user selections.

I have a dataset that returns volume by:

• Month
• Company
• Activity Group

What I'm looking to do:

• Based on company selection:
  • Identify the top-5 "Groups" ordered by volume DESC across the entire dataset.
    • Again, this is based on either ALL companies in the dataset, or a user-selected company.
  • Using those "Groups", create a table that displays, by Month, the Volume by Group

I've attached an image that points out what I'm trying to accomplish.

• The left-most portion is simply the raw dataset
• To the right...
  • The top-3 datasets show the ranking of the "Groups" by volume desc based on the entire dataset; this needs to be dynamic based on the selection (or lack of) by company
    • I only want the top-5 groups--in this example, groups ranked 6 & 7 should be ignored
  • The bottom-3 datasets show the resulting tables
    • Volume by Month and [pre-determined] Group

I hope this makes sense.

Thanks in advance!

[v-jiascu-msft · Accepted solution]

Hi @ww1711,

Please check out the demo in the attachment. Since there are some groups that don't exist in some days, we need to create a [Groups] table first.

1. Create a new table.

Groups = VALUES(Table1[Group])

2. Create a measure.

Rank =
VAR ranks =
    CALCULATE (
        RANKX ( ALL ( Groups[Group] ), CALCULATE ( SUM ( Table1[Volume] ) ) ),
        ALL ( Table1[Month] )
    )
RETURN
    IF ( ranks <= 5, ranks, BLANK () )

Best Regards,
Dale

[ww1711]

Really fantastic stuff here, Dale. Thank you for your assistance.

One last ask regarding this... I've rendered what you've got in the table in a stacked column chart and I'm curious as to whether or not I can bypass the automatic coloring of the series. I've got company standard color guidelines that I'd like to apply to each rank at render-time. Is this possible?

Ex: Rank 1 gets #FF0000, 2 gets #FFFF00, etc... It should be mentioned that I'm using PBI Desktop for Report Server (March 2018).

Thanks in advance!

[v-jiascu-msft]

Hi @ww1711,

Maybe you can customize the color in the settings. Please refer to the snapshot below. How does the Stacked Column chart look like?

Best Regards,
Dale