Forum Discussion

RobvB's avatar
RobvB
Frequent Visitor
5 years ago
Solved

RANKX using a measure

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 ...
Need Help
  • RobvB's avatar
    RobvB
    5 years ago

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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

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

RobvB's avatar
RobvB
Frequent Visitor
5 years ago

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's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.