Adding measures to table breaks RANKX

[Anonymous]

Hi guys,

I'm trying to build a table visual with a ranking of products by most lucrative to least, but it's not working out for me.

I have a big table of sales transactions (summarized a bit, one row per customer per product). This is linked to a separate "dim_product" table.

What I want is a table that ranks the products based on the transactions (1,2,3,4,5,6,...), then has the name of the product, what product type it is, the annual value of it (i.e. the raw value that the ranks were based on), and also includes some other measures about the products.

My RANKX formula	'dim_product'[Product Name]	'dim_product'[Product Type]	ValTY	<...other measures>
1	Product A	Apples	180
2	Product B	Apples	120
3	Product C	Oranges	90

This is my Product Rank measure = 

RANKX(ALLSELECTED('dim_product'[Product Name],'dim_product'[Product Type]),[ValTY]), where [ValTY] comes from the 'Transaction Data' table and is simply the sum of the $ value of the transactions, i.e. ValTY = SUM(Value TY).

This all works fine. The problem comes when I try to drag in other measures to the right of ValTY, i.e. such "Value Change vs Previous Year", which can be calculated from the 'Transaction Data' table (Value TY/Value YA). As soon as I do this, the RANKX gets completely out of whack, for example, there is suddenly no "no. 1." ranked product and the ranking starts from 2, and there are two "no. 2"s and two "no. 19"s and no "no. 18"s, even though the "ValTY" figures are the same as they were and no ties in them.

Has anyone encountered this sort of thing before/know how to fix it? I'm guessing my RANKX formula needs to be adjusted:

RANKX(ALLSELECTED('dim_product'[Product Name],'dim_product'[Product Type]),[ValTY])

[amitchandak]

@Anonymous , Other measures should not create a problem  , but if you use any other non summarized columns then 'dim_product'[Product Name],'dim_product'[Product Type], then the rank will inside that column.

For example, if you create Rank on City and use city id in visual you will get Rank only 1. Because city will rank inside city id

refer if needed

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Anonymous]

Hi @amitchandak ,

Yep, I understood that - but unfortunately the measures/summarized columns are causing a problem  for me somehow...

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous · Accepted solution]

Hi @amitchandak ,

Thanks for the offer of help - I solved this issue myself in the end. Somehow the architecture of my model was meaning that the new measure was filtering/itself acting as a context for the RANKX calculation. This was not the case in a simpler version I built in a new PBIX based on just the info I provided above.

In any case, the DAX I needed in my actual model was:

ValChg% = var that = SELECTEDVALUE(dim_product[dim_product_key])

RETURN

IFERROR(CALCULATE([Value TY]/[Value YA]-1,ALL('Transaction Data'),'Transaction Data'[dim_product_key]=that),BLANK())