The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 😞
Solved! Go to 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 )
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.
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!
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 )
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.
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% |
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 )
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.
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.
User | Count |
---|---|
109 | |
82 | |
74 | |
53 | |
49 |
User | Count |
---|---|
133 | |
97 | |
94 | |
71 | |
69 |