Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Lionel_Charley
Regular Visitor

Power BI Top 5 sales

Hi,

 

I need help with Top 5 sales. My power table looks like the first table. I need to transform the data as the second table that shows top 5 sales and rest is categorised under others

 

 

Lionel_Charley_0-1679642388245.png

 

1 ACCEPTED SOLUTION
Anand24
Super User
Super User

Hi @Lionel_Charley ,
Create below 2 calculated columns:

Place Rank = 
RANKX(
    ALL('Sales by Place'[Place]),
    CALCULATE(
            SUM ( 'Sales by Place'[Sales]),
            ALLEXCEPT('Sales by Place', 'Sales by Place'[Place])
    )
)
New Place = 
IF(
    'Sales by Place'[Place Rank] >= 1 && 'Sales by Place'[Place Rank] <= 5, 'Sales by Place'[Place],
    "Others"
)
 
Result:
Anand24_0-1679645845876.png

Let me know if you require the .pbix.

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

View solution in original post

3 REPLIES 3
Lionel_Charley
Regular Visitor

Thank you all for the support

Anand24
Super User
Super User

Hi @Lionel_Charley ,
Create below 2 calculated columns:

Place Rank = 
RANKX(
    ALL('Sales by Place'[Place]),
    CALCULATE(
            SUM ( 'Sales by Place'[Sales]),
            ALLEXCEPT('Sales by Place', 'Sales by Place'[Place])
    )
)
New Place = 
IF(
    'Sales by Place'[Place Rank] >= 1 && 'Sales by Place'[Place Rank] <= 5, 'Sales by Place'[Place],
    "Others"
)
 
Result:
Anand24_0-1679645845876.png

Let me know if you require the .pbix.

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

Ahmedx
Super User
Super User

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.