Lowest 5 Sales measure

[gbarr12345]

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]))

[v-linyulu-msft]

Hi,@gbarr12345

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

Best Regards,

Leroy Lu

[gbarr12345]

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 · Accepted solution]

@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?

[parry2k]

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

[gbarr12345]

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.

[parry2k]

@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?

[gbarr12345]

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.

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

[parry2k]

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

[gbarr12345]

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] ) )