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
icdns
Post Patron
Post Patron

Dynamic based on filters selected: Top 5 and Others (RANK)

Hi everyone, 

 

Would like to ask for your help as I have a list of Categories and I want to group them by Top 5 and the rest will be grouped as "Others" and as I filter my dimension the Ranking of the categories will by dynamic depending on the filter selected. 

 

Here's my list: 

 

Sales Contribution = (Sales / Total Sales)

CategoryRankSalesSales Contribution %
Kitchen197,00020.86%
Car Accessories289,56619.26%
School Supplies378,90116.97%
Food Groceries455,32211.90%
Pet Supplies545,8889.87%
Household Supplies633,8887.29%
Phone Gadgets721,4014.60%
Bathroon Accessories818,6784.02%
Garden Accessories913,8992.99%
Baking Materials1010,4442.25%
Overall Total 464,987100.00%

 

And I want my result to be like this: 

 

As you can see, the Sales & Sales Contribution of Others are added added. 

CategoryRankSalesSales Contribution %
Kitchen197,00020.86%
Car Accessories289,56619.26%
School Supplies378,90116.97%
Food Groceries455,32211.90%
Pet Supplies545,8889.87%
Others (Excluding the top 5)      98,31021.14%

 

I tried creating another table with Category & Others and doing RankX but I'm not getting the result I want 😞 

Top 5 & Others = UNION(VALUES(Table[Category]), ROW("Category", "Others"))
 
Could somebody help me? Thank you in advance! 
1 ACCEPTED SOLUTION

Hi,

Thank you for your message.

Could you please check the below and the attached pbix file, whether it suits your requirement?

 

Picture1.png

 

New Table 2 =
VAR _topNnumber = 5
VAR _totalsales =
    SUM ( Sales2[Sales] )
VAR _topfivesales =
    CALCULATE (
        SUM ( Sales2[Sales] ),
        TOPN (
            5,
            SUMMARIZE ( Sales2, Sales2[Category] ),
            CALCULATE ( SUM ( Sales2[Sales] ) ), DESC
        )
    )
VAR _topfivetable =
    ADDCOLUMNS (
        TOPN (
            _topNnumber,
            SUMMARIZE ( Sales2, Sales2[Category] ),
            CALCULATE ( SUM ( Sales2[Sales] ) ), DESC
        ),
        "@Sales", CALCULATE ( SUM ( Sales2[Sales] ) ),
        "@Ranking",
            RANKX (
                SUMMARIZE ( Sales2, Sales2[Category] ),
                CALCULATE ( SUM ( Sales2[Sales] ) ),
                ,
                DESC
            ),
        "@contribution", FORMAT ( CALCULATE ( SUM ( Sales2[Sales] ) ) / SUM ( Sales2[Sales] ), "#0.00%" )
    )
VAR _otherstable =
    {
        ( "Others", _totalsales - _topfivesales, _topNnumber + 1, FORMAT ( ( _totalsales - _topfivesales ) / _totalsales, "#0.00%" ) )
    }
RETURN
    UNION ( _topfivetable, _otherstable )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
icdns
Post Patron
Post Patron

Hi @Jihwan_Kim 

 

Sorry I can't give you the exact powerbi file due to privacy concern but I can share you a similar data set. 🙂 

Data set: 

 

Store CodeCategorySales
Store AKitchen       10,000
Store BKitchen       10,500
Store CKitchen       10,233
Store ACar Accessories          9,900
Store BCar Accessories          9,976
Store CCar Accessories          9,988
Store ASchool Supplies          8,876
Store BSchool Supplies          8,865
Store CSchool Supplies          8,954
Store AFood Groceries          7,655
Store BFood Groceries          7,765
Store CFood Groceries          7,888
Store APet Supplies          6,666
Store BPet Supplies          6,655
Store CPet Supplies          6,443
Store AHousehold Supplies          5,476
Store BHousehold Supplies          5,463
Store CHousehold Supplies          5,577
Store ABathroon Accessories          4,888
Store BBathroon Accessories          4,909
Store CBathroon Accessories          4,382
Store AGarden Accessories          3,788
Store BGarden Accessories          3,888
Store CGarden Accessories          3,222
Store ABaking Materials          2,100
Store BBaking Materials          2,000
Store CBaking Materials          2,883
TOTAL     178,940

 

Expectation: 

CategoryRankSalesSales Contribution %
Kitchen130,73317.18%
Car Accessories229,86416.69%
School Supplies326,69514.92%
Food Groceries423,30813.03%
Pet Supplies519,76411.05%
OTHERS648,57627.15%
Overall Total 178,940100.00%

 

Thank you in advance!

Hi,

Thank you for your message.

Could you please check the below and the attached pbix file, whether it suits your requirement?

 

Picture1.png

 

New Table 2 =
VAR _topNnumber = 5
VAR _totalsales =
    SUM ( Sales2[Sales] )
VAR _topfivesales =
    CALCULATE (
        SUM ( Sales2[Sales] ),
        TOPN (
            5,
            SUMMARIZE ( Sales2, Sales2[Category] ),
            CALCULATE ( SUM ( Sales2[Sales] ) ), DESC
        )
    )
VAR _topfivetable =
    ADDCOLUMNS (
        TOPN (
            _topNnumber,
            SUMMARIZE ( Sales2, Sales2[Category] ),
            CALCULATE ( SUM ( Sales2[Sales] ) ), DESC
        ),
        "@Sales", CALCULATE ( SUM ( Sales2[Sales] ) ),
        "@Ranking",
            RANKX (
                SUMMARIZE ( Sales2, Sales2[Category] ),
                CALCULATE ( SUM ( Sales2[Sales] ) ),
                ,
                DESC
            ),
        "@contribution", FORMAT ( CALCULATE ( SUM ( Sales2[Sales] ) ) / SUM ( Sales2[Sales] ), "#0.00%" )
    )
VAR _otherstable =
    {
        ( "Others", _totalsales - _topfivesales, _topNnumber + 1, FORMAT ( ( _totalsales - _topfivesales ) / _totalsales, "#0.00%" ) )
    }
RETURN
    UNION ( _topfivetable, _otherstable )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim ,

 

Thank you so much! This one worked! 🙂 

However, is it possible to exclude one Category from the Ranking? Let's say the Kitchen..

So in this case Car Accessories is the top 1 🙂  And also can be dynamically filtered by a dimension? Lets say the store code? 🙂 

 

CategoryRankSalesSales Contribution %
Kitchen130,73317.18%
Car Accessories229,86416.69%
School Supplies326,69514.92%
Food Groceries423,30813.03%
Pet Supplies519,76411.05%
OTHERS648,57627.15%
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Picture1.png

 

New Table = 
VAR _totalsales =
    SUM ( Sales[Sales] )
VAR _topfivesales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        TOPN ( 5, Sales, CALCULATE ( SUM ( Sales[Sales] ) ), DESC )
    )
VAR _topfivetable =
    ADDCOLUMNS (
        TOPN ( 5, Sales, CALCULATE ( SUM ( Sales[Sales] ) ), DESC ),
        "@Ranking", RANKX ( Sales, CALCULATE ( SUM ( Sales[Sales] ) ),, DESC ),
        "@contribution", FORMAT ( CALCULATE ( SUM ( Sales[Sales] ) ) / SUM ( Sales[Sales] ), "#0.00%" )
    )
VAR _otherstable =
    {
        ( "Others", _totalsales - _topfivesales, "otherranking", FORMAT ( ( _totalsales - _topfivesales ) / _totalsales, "#0.00%" ) )
    }
RETURN
    UNION ( _topfivetable, _otherstable )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim ,

 

I tried your formula but I am getting this error..

"Each table argument of 'UNION' must have the same number of columns."

 

😞

Hi,

I think your sales table has more columns than what I have in my sample pbix file.

Please share your sample pbix file's link, and then I can try to look into it to come up with a more relevant solution for your dataset.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.