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

[icdns]

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)

Category	Rank	Sales	Sales Contribution %
Kitchen	1	97,000	20.86%
Car Accessories	2	89,566	19.26%
School Supplies	3	78,901	16.97%
Food Groceries	4	55,322	11.90%
Pet Supplies	5	45,888	9.87%
Household Supplies	6	33,888	7.29%
Phone Gadgets	7	21,401	4.60%
Bathroon Accessories	8	18,678	4.02%
Garden Accessories	9	13,899	2.99%
Baking Materials	10	10,444	2.25%
Overall Total		464,987	100.00%

And I want my result to be like this: 

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

Category	Rank	Sales	Sales Contribution %
Kitchen	1	97,000	20.86%
Car Accessories	2	89,566	19.26%
School Supplies	3	78,901	16.97%
Food Groceries	4	55,322	11.90%
Pet Supplies	5	45,888	9.87%
Others (Excluding the top 5)		98,310	21.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! 

[icdns]

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 Code	Category	Sales
Store A	Kitchen	10,000
Store B	Kitchen	10,500
Store C	Kitchen	10,233
Store A	Car Accessories	9,900
Store B	Car Accessories	9,976
Store C	Car Accessories	9,988
Store A	School Supplies	8,876
Store B	School Supplies	8,865
Store C	School Supplies	8,954
Store A	Food Groceries	7,655
Store B	Food Groceries	7,765
Store C	Food Groceries	7,888
Store A	Pet Supplies	6,666
Store B	Pet Supplies	6,655
Store C	Pet Supplies	6,443
Store A	Household Supplies	5,476
Store B	Household Supplies	5,463
Store C	Household Supplies	5,577
Store A	Bathroon Accessories	4,888
Store B	Bathroon Accessories	4,909
Store C	Bathroon Accessories	4,382
Store A	Garden Accessories	3,788
Store B	Garden Accessories	3,888
Store C	Garden Accessories	3,222
Store A	Baking Materials	2,100
Store B	Baking Materials	2,000
Store C	Baking Materials	2,883
TOTAL	178,940

Expectation: 

Category	Rank	Sales	Sales Contribution %
Kitchen	1	30,733	17.18%
Car Accessories	2	29,864	16.69%
School Supplies	3	26,695	14.92%
Food Groceries	4	23,308	13.03%
Pet Supplies	5	19,764	11.05%
OTHERS	6	48,576	27.15%
Overall Total		178,940	100.00%

Thank you in advance!

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

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

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 )

[icdns]

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? 🙂 

Category	Rank	Sales	Sales Contribution %
Kitchen	1	30,733	17.18%
Car Accessories	2	29,864	16.69%
School Supplies	3	26,695	14.92%
Food Groceries	4	23,308	13.03%
Pet Supplies	5	19,764	11.05%
OTHERS	6	48,576	27.15%

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

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 )

[icdns]

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."

😞

[Jihwan_Kim]

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.