RANKX using a measure

[RobvB]

I have a table with sales transactions.

These are item level transactions, so multiple transactions per item/store/date/etc.

First, I want a total per store, so I create this measure:

total $ per store =

CALCULATE(

    SUM('ITK_Retail_AM_ITKRetailTransactionsView'[AALINEAMOUNT]),'ITK_Retail_AM_ITKRetailTransactionsView'[OMOPERATINGUNITNUMBER])

So sum AALineAmount for each store (OperatingUnitNumber)

Now I want the top 5 of $ per store. So I create another measure:

Rank $ per store = rankx(ITK_Retail_AM_ITKRetailTransactionsView, ITK_Retail_AM_ITKRetailTransactionsView[total $ per store])

But it returns ‘1’ for each store… What am I missing?

[RobvB · Accepted solution]

I got it to work. My problem was that I was using the transaction table as the 'main' table, but since I want ranking per store, I should use the store table.

Rank Sales Per Store = rankx(ALL(ITK_Retail_AM_OMOperatingUnitEntity), ITK_Retail_AM_ITKRetailTransactionsView[Total Sales Per Store])

[v-henryk-mstf]

Hi @RobvB ,

If you get the correct answer yourself, you can mark it as the correct answer.If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-henryk-mstf]

Hi @RobvB ,

According to your current description, you want to get the top 5 of $ per store. Try a simple method, use Top N in "Filters on this visual", refer to the following:
Put the field in By value.

Then, put the Store in the slicer and filter the top5 items corresponding to different stores.

If the problem is still not resolved, Please describe the exact requirements and point out detailed error messages. It is best to provide sample pbix file so that I can do the correct test, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Do you want the total sale of the Top 5 items per store?  Share some data and show the expected result. 

[RobvB]

Basically I want the top 5 of the total sales per store. So I first need to get total sales per store, which I've done by creating the 1st measure, then I want to take the top 5 of that measure, which I tried with the second measure.

Added complexity is that this *has* to be done using direct query only.

Date looks like this:

[Ashish_Mathur]

Hi,

We should be able to write a measure to do this but i am still not very clear of your expected result.  Share some data (say 50 rows) and show your exact expected result. 

[lbendlin]

https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/