Forum Discussion

Thackeb's avatar
Thackeb
Icon for Advocate I rankAdvocate I
7 years ago

Rankx with Read Only Tables

I'm a new user and creating a Top Items template. The Data houses sales and I want to rank the items by those sales.

 

Rankx works if I upload my own data via Excel and can access the other options, such as adding a column. My main data tables are Read-Only and I've been unable to make Rankx work.

 

Header example..........Items---Item Desc---Times Ordered-----Sales $

 

I want to rank Item based on Sales $. I've tried the below but I get the same number. I only have option to "Report" and because of the Read-only tables, cannot add "New Column" on the Home tab.

 

Rank = RANKX(TOPN(300,ALLSELECTED('Codes'),[Sales $],ASc),10, ,ASC,Skip)
Rank 2 = RANKX('Codes',[Sales $], ,ASC,Skip)
 
This worked when I uploaded my own Excel data and I was able to go to the Data section.
Sales Rank = RANKX(Sheet1,[Sal Amt], ,DESC)
 
Thanks!
Need Help

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Thackeb 

    You could create measures

    Measure-sales = CALCULATE(SUM(Sheet4[sales]),ALLEXCEPT(Sheet4,Sheet4[item]))

Measure = RANKX(ALL(Sheet4),[Measure-sales],,ASC,Dense)

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Thackeb's avatar
      Thackeb
      Icon for Advocate I rankAdvocate I
      7 years ago

      Thank you for the tips. Still not working. lol.

       

      I created this measure....which works in the table.  Xmargin and Item are the read-only Data Tables that I'm utilizing.

       

      Measure = CALCULATE(SUM(XMargin[ Net Sales Amount2]),ALLEXCEPT('Item','Item'[Item]))
       
      This one makes the table X out.....
       
      Measure Rank = RANKX(ALL('Item'[Item]),[Measure],,ASC,Dense)
       
      Thoughts???
      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Thackeb 

        It seems you have only "report" view enabled.

        So it is impossible to create relationships between "XMargin" table and "Item" table.

        I make a test with this scenario, If you also have "item" column in "XMargin" table, modify your first measure as below

        Measure = CALCULATE(SUM(XMargin[Net Sales Amount2]),ALLEXCEPT(XMargin,XMargin[Item]))

        Then modify the rank measure as below

        Rank = RANKX(ALL(XMargin),[Measure],,ASC,Dense)

        Best Regards

        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.