Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All.
I need to get the top 2 client group sales and the rest is to be shown as others. TopN client (number [No.]) will be selected from the filters (Advance filter) and rest all clients should show as other.
What best measure should I use to this result?
Solved! Go to Solution.
Hi @gauravnarchal ,
1. Create a new table manually like this:
2. Create these measures:
Total Sales =
CALCULATE (
SUM ( 'Table'[Sale Amt] ),
ALLEXCEPT ( 'Table', 'Table'[Client Group] )
)
Sales Amount Top =
CALCULATE (
[Total Sales],
KEEPFILTERS ( TOPN ( 2, ALL ( 'Table'[Client Group] ), [Total Sales] ) )
)
Sales Amount Other =
CALCULATE (
[Total Sales],
KEEPFILTERS (
EXCEPT (
ALL ( 'Table'[Client Group] ),
TOPN ( 2, ALL ( 'Table'[Client Group] ), [Total Sales] )
)
)
)
Sales Amount Other Total Only =
IF ( NOT ( ISFILTERED ( 'Table'[Client Group] ) ), [Sales Amount Other] )
Sales Amount Top & Others =
IF (
HASONEVALUE ( 'Top & Other'[Top] ),
SWITCH (
VALUES ( 'Top & Other'[Top] ),
"Top2", [Sales Amount Top],
"Other", [Sales Amount Other Total Only]
),
[Sales Amount Top]
)
3. Use a Matrix visual to show the result:
Attached my sample file that hopes to help you: How to create Power BI TOP N and others Report.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @gauravnarchal ,
1. Create a new table manually like this:
2. Create these measures:
Total Sales =
CALCULATE (
SUM ( 'Table'[Sale Amt] ),
ALLEXCEPT ( 'Table', 'Table'[Client Group] )
)
Sales Amount Top =
CALCULATE (
[Total Sales],
KEEPFILTERS ( TOPN ( 2, ALL ( 'Table'[Client Group] ), [Total Sales] ) )
)
Sales Amount Other =
CALCULATE (
[Total Sales],
KEEPFILTERS (
EXCEPT (
ALL ( 'Table'[Client Group] ),
TOPN ( 2, ALL ( 'Table'[Client Group] ), [Total Sales] )
)
)
)
Sales Amount Other Total Only =
IF ( NOT ( ISFILTERED ( 'Table'[Client Group] ) ), [Sales Amount Other] )
Sales Amount Top & Others =
IF (
HASONEVALUE ( 'Top & Other'[Top] ),
SWITCH (
VALUES ( 'Top & Other'[Top] ),
"Top2", [Sales Amount Top],
"Other", [Sales Amount Other Total Only]
),
[Sales Amount Top]
)
3. Use a Matrix visual to show the result:
Attached my sample file that hopes to help you: How to create Power BI TOP N and others Report.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
I would create a calculated table based on your distinct client groups.
then add the following columns ot the table :
-Sales = Sum(sales amnt)
-rank = RANKX(All(client groups), [Sales],,,Dense)
-topn = if([rank] <= 2 , "Top 2", "Other" )
Does this make sense?
@Anonymous It is not summarizing others in one row. See below screenshot.
can you e-mail me a copy of the PBIX file and i can see what i can do to fix it 🙂
this is the end result of the matrix
send me your e-mail and i can send your way sorry one drive is giving me issues tonight not enough coffee for it 🙂
can you send me your e-mail it wont attach the link for some reason
it is all fixed and is a good solution
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |