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.
Solved! Go to Solution.
Please try this expression in your calculated column to get your desired result.
Report Category =
VAR __thiscategory = Products[Category]
VAR __thiscategorycount =
CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
VAR __summary =
ADDCOLUMNS (
SUMMARIZE ( Products, Products[Category] ),
"@productcount", CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
)
VAR __thiscatrank =
RANKX ( __summary, [@productcount], __thiscategorycount, DESC, SKIP )
RETURN
IF ( __thiscatrank <= 5, "C" & __thiscatrank, "Other" )
I wasn't clear if the column you gave was an example of expected output or not. If you want it to say "Top5Category", just replace the true term in the IF() with that text instead of "C" & __thiscatrank.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression in your calculated column to get your desired result.
Report Category =
VAR __thiscategory = Products[Category]
VAR __thiscategorycount =
CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
VAR __summary =
ADDCOLUMNS (
SUMMARIZE ( Products, Products[Category] ),
"@productcount", CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
)
VAR __thiscatrank =
RANKX ( __summary, [@productcount], __thiscategorycount, DESC, SKIP )
RETURN
IF ( __thiscatrank <= 5, "C" & __thiscatrank, "Other" )
I wasn't clear if the column you gave was an example of expected output or not. If you want it to say "Top5Category", just replace the true term in the IF() with that text instead of "C" & __thiscatrank.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Excellent! It is working.
Thanks a lot @mahoneypat !
Could you please explain how is it working?
If you have a link to a similar solution - great
Thanks!
Glad it is working for you. Here is the expression with comments to explain how it works
Report Category =
VAR __thiscategory = Products[Category] //store this category in a variable
VAR __thiscategorycount =
//calculate the count for this category removing all filters except for the category
CALCULATE (
COUNTROWS ( Products ),
ALLEXCEPT ( Products, Products[Category] )
)
VAR __summary =
//make a virtual table of all the categories with their respective counts
ADDCOLUMNS (
SUMMARIZE ( Products, Products[Category] ),
"@productcount", CALCULATE ( COUNTROWS ( Products ), ALLEXCEPT ( Products, Products[Category] ) )
)
VAR __thiscatrank =
// find the rank for this category (using the variable as 3rd term in RANKX)
RANKX (
__summary,
[@productcount],
__thiscategorycount,
DESC,
SKIP
)
RETURN
IF ( __thiscatrank <= 5, "C" & __thiscatrank, "Other" )
//if <=5 concat the rank with C otherwise put "Other"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@michaelsh , refer if these can help
https://community.powerbi.com/t5/Desktop/Top-5-and-others/td-p/165945
https://www.youtube.com/watch?v=UAnylK9bm1I
Hi @michaelsh ,
https://community.powerbi.com/t5/Desktop/Top-5-by-Category/m-p/754307
https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
This is not my issue. I went through a lot of RANKX TOPN articles before posting this question.
My challenge is:
For every product - to calculate the rank of ITS CATEGORY.
NOT the rank of a product within category, but the rank of a CATEGORY by the number of products.
Thanks
Here is the file:
https://1drv.ms/u/s!AoP_9ampPIT77kP8Eh0-vFgwvKof?e=PQ65gP
You can see in the picture that I am trying to create a calculated column - ReportCategory that shows only top 5 categories (C1-C5).
For this I need to calculate number of products for each category (hand written in green), rank the categories and then create "if rank <=5 then category else "Others"
Something like that
Please help
Thanks a lot
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 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |