Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |