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!

Post Patron

4 years ago

Solved

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)

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!

Need Help

Jihwan_Kim

4 years ago

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.pbix31 KB

6 Replies

Jihwan_Kim

Super User

4 years ago

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.pbix21 KB

icdns
Post Patron
4 years ago
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
  Super User
  4 years ago
  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.

icdns

Post Patron

4 years ago

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

Super User

4 years ago

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.pbix31 KB

icdns
Post Patron
4 years ago
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%

Forum Discussion

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

6 Replies

Related content

Dynamic Filter

Dynamic Filtering based on date selection

Dynamic Filtering

Dynamic text based on filter selection

How to filter a slicer dynamically based on selection on field parameter