RANKX returning only value of 1

[Anonymous]

I have a table with Item/Product and sales.  I have read a lot of the postings and I'm still stuck.

I created the following:

Measure:  total demand amount = sumx('rpt salestodatemetrics', [wtd_demandamt]))

Measure: Demand Rank = Rankx(all('rpt salestodatemetrics'[itemid]), [total demand amount], ,DESC)

When I pull demand rank into table below - you can see the values are all '1'.  The rpt salestodatemetrics has a count of over 7k distinct items.

Ideally, I'd like to be able to be able to create dynamic visuals that can either show our top selling or lowest selling items and what proportion % of the sales they make up.

Any help would be appreciated.

 Item Sales Dat

[jdbuchanan71]

Try it without the column reference in the ranking measure.

Demand Rank = RANKX ( ALL ( 'rpt salestodatemetrics' ), [total demand amount], ,DESC )

[Anonymous]

@jdbuchanan71 Thanks for that message. I tried that option and still get 1s.  I'm pulling the following fields into the report.

rpt salestodatemetrics fields:  [itemid], [itemname]

key measures fields: [total demand amt], [demand rank]

if i used my original code and removed [itemname] field the rank did seem to work.  That lead me to try the code you mentioned and be able to place [itemname] back in the report.  No luck.  

I do have a filter on the report, which I hope to replace using this rankx feature of the following

[itemid] Top N of 30 by value [total demand amount]

[MFelix]

Hi @Anonymous,

Try to wrap your measure with a calculate formula something like this
Demand Rank =
RANKX (
ALL ( 'rpt salestodatemetrics'[itemid] ),
CALCULATE ( [total demand amount] )
)

Regards,
MFelix

[jdbuchanan71 · Accepted solution]

What if you include both the fields in the ALL?

Demand Rank = RANKX ( ALL ( 'rpt salestodatemetrics'[itemid],'rpt salestodatemetrics'[ItemName]  ), [total demand amount], ,DESC )

[Anonymous]

@jdbuchanan71 That seems to work.  Strange that just the ALL('rpt salestodatemetrics') with no fields selected wouldn't work.  That is what no fields included is supposed to mean correct?

This salestodatemetrics table is super large.  It has probably over 100+ columns.

[jdbuchanan71]

Yes, the all ( Table ) should do the same but given the size of the table you are probably better off listing the columns, otherwise the filter has to generate over every one of the columns which would be slower.