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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gbarr12345
Post Patron
Post Patron

Lowest 5 Sales measure

Hi there,

 

I'm trying to find ameasure that will show me the lowest 5 Sales. I have a measure that shows the top 5 sales that works for me but is there a measure to show the lowest?

 

The top 5 measure is below. Any help would be appreciated.

 

Measure = CALCULATE([S],TOPN(3,ALL(Table Name[Product Name]),[Total Sales]),VALUES(Table Name[Product Name]))

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@gbarr12345 as I said before, whatever column you are using to join these tables, some of the values exists in the Module Sales with inventory' but doesn't exists in the dimension table. 

 

For example, tables are joined on product id, inventory table has some id's which doesn't exists in the dimension table. Can you double check your data?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
v-linyulu-msft
Community Support
Community Support

Hi,@gbarr12345

 

Can you tell me if your problem is solved? If yes, please accept it as solution.

 

Best Regards,

Leroy Lu


 

Hi Leroy,

 

Apologies, I was out of office and away from my laptop for a while.

 

Yes the problem is solved so thanks to everyone for their help!

parry2k
Super User
Super User

@gbarr12345 as I said before, whatever column you are using to join these tables, some of the values exists in the Module Sales with inventory' but doesn't exists in the dimension table. 

 

For example, tables are joined on product id, inventory table has some id's which doesn't exists in the dimension table. Can you double check your data?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@gbarr12345 seems like you have description values tables that don't exist in the dimension table and that is probably causing this issue.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I have a dimension_item table which includes the item description field and I have a 'Module Sales with inventory' table which includes the amount field where I created the Total Amount measure. I'm trying to make the measure to include both fields from both tables. It worked with the top 5 but struggling to get it to work with the bottom 5.

 

gbarr12345_0-1715124217553.png

 

gbarr12345_1-1715124241635.png

 

parry2k
Super User
Super User

@gbarr12345 what is total amount measure? If you put description and total amount measure in a table visual, do you see descriptions with blank amount?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

The total amount measure is essentially the Total Sales - 

Total Amount = SUM('Module Sales with Inventory'[amount])
 
Data appears when I pull in the description and total amount.

 

gbarr12345_1-1715123597466.png

 

It also shows data when I pull in the description and Top 5 sales:

 

gbarr12345_3-1715123808244.png

 

 

 

parry2k
Super User
Super User

@gbarr12345 add ASC keyword after [Total Sales] by default it uses DESC.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi Parry,

 

I tried that but it's showing blank.

 

Top 5 Lowest Sales = CALCULATE([Total Amount],TOPN(5,ALL('Dimension Item'[description]), [Total Amount],  ASC), VALUES('Dimension Item'[description] ) )

 

gbarr12345_0-1715123166428.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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