Forum Discussion

ww1711's avatar
ww1711
Frequent Visitor
7 years ago
Solved

Dynamic table creation

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!

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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 () )

    Dynamic-table-creation2

     

    Best Regards,
    Dale

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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 () )

    Dynamic-table-creation2

     

    Best Regards,
    Dale

    • ww1711's avatar
      ww1711
      Frequent Visitor
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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?

        Dynamic-table-creation3

         

        Best Regards,
        Dale