Rank on Calculated measure

[Nobie]

Hi , 
 I need help plz provide the solution.
Below is a pseudo data for understanding and currently working in Direct query .
i have line and stacked bar chart . 

in x-axis I have 2 dimension Product and checks. Checks contains T,F,N values.
In columns y axis I have a calculated measure sales and  Legend is category columns.
I have several filters and paramters applied.
I am trying to create a rank function ,
code:    RANKS( 
                        SUMMARIZE( ALLSELECTED( 'Table'),  'Table'[Product],'Table[Checks],'Table'[Category]),
                       [Sales], ,DESC,DENSE)
but for 2 product i am getting same rank and but 2 product didn't have the same values .

Please help in these.

[v-xinruzhu-msft · Accepted solution]

Hi @Nobie 

You can refer to the following measure

Measure = var a=SUMMARIZE(ALLSELECTED('Table'),[Product],"Sum",SUM('Table'[Sales]))
var b=ADDCOLUMNS(a,"Rank",RANKX(a,[Sum],,DESC,Dense))
return MAXX(FILTER(b,[Product] in VALUES('Table'[Product])),[Rank])

Output

Best Regards!

Yolo Zhu

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

[Nobie]

Can u plz tell me what does statement after return doing?

[vanessafvg]

can you provide some sample data with your expected solution?

[Nobie]

i have attached a excel .
basically i want the rank on the basis on Product on basis on sales and line and checks to be ignored .

[vanessafvg]

Hi thanks, but where is the attachment?  I need the data in text format to do this. 

[Nobie]

this is the table .

Product	Line	Checks	Sales
Product A	L1	N	2601
Product A	L1	N
Product A	L1	T	23
Product A	L2	N
ProductB	L1	N	538
ProductB	l1	T	130
ProductB	L2	N
Product C	L1	N	277
Product C	L1	T	15
Product C	L2	N

this is the output table

Product	Line	Checks	Sales	RANKS
Product A	L1	N	2601	1
Product A	L1	N		1
Product A	L1	T	23	1
Product A	L2	N		1
ProductB	L1	N	538	2
ProductB	l1	T	130	2
ProductB	L2	N		2
Product C	L1	N	277	3
Product C	L1	T	15	3
Product C	L2	N		3

below is the summary

Product	sales	Ranks
Product A	2624	1
Product B	668	2
Product C	292	3

[vanessafvg]

hi see attached.  you will need a product table for this solution that needs to be joined to your table.

Product Rank =
IF (
ISINSCOPE ( 'Product'[Product] ),
RANKX ( ALLSELECTED ( 'Product'[Product] ), CALCULATE ( [total sales] ) )
)

the inscope is to prevent the rank from showing at the total level

[Nobie]

Hi, Thanks for the help that didn't work
this didn't worked. I am attaching google drive link excel 

[vanessafvg]

when you said it didn't work, please make sure you explain what it is your are expecting so we on the same page.  I am assuming you mean its saying rank 1,3 and you wanting rank 1,2.

all you need to do is change your rank to allselected rather than all and then it will only use the selected values.  Otherwise it will scan all your data regardless of what you are selecting.

RNK =
RANKX (
ALLSELECTED ( 'data 1'[Product], 'data 1'[Product] ),
CALCULATE ( [Sum Of Sales], ALLEXCEPT ( 'data 1', 'data 1'[Product] ) ),
,
DESC,
DENSE
)