Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ww1711
Frequent Visitor

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

 

top5results.png

 

 

I hope this makes sense.

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.