Rankx with Read Only Tables

[Thackeb]

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!

[v-juanli-msft]

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]

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]

Hi @Thackeb 

Is this problem sloved?

If not, please let me know.

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]

Thank you for your efforts and  help. I tried your suggestions and it still didn't work. I've suggested that our IT team open up the report briefly to the Data view so I can add the column. Since it's not solved but I'm at an empasse, should I still mark it solved?

[v-juanli-msft]

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.