Rankx problem with column linked being used

[Anonymous]

I am trying to create a ranking on stores and their each respective sales over category.

The result should look like:

Store	Category	Sales	Ranking
New York	Fruit	50	2
	Electronics	60	1
	Clothing	45	3
Paris	Fruit	45	3
	Electronics	55	2
	Clothing	55	2
Singapore	Fruit	20	4
	Electronics	10	4
	Clothing	60	1
Berlin	Fruit	80	1
	Electronics	40	3
	Clothing	30	4
Los Angeles	Fruit	10	5
	Electronics	8	5
	Clothing	5	5

I have a factsales ('sales) table, a dimension store table ('store) and a dim category table ('category). Tables are linked many to one (single) based on keys: a generated category and storekey.

My ranking formula looks like:

RANKX (

ALL( Store );

CALCULATE (

[Sales] )

)

When I try to create above grid with the 'Store[Store] on the axis, I see where numbers are missing or some are double. However, if I dont put 'Store[Store] but I use 'Store'[Storekey], the ranking is working correctly. How can I fix this rather than using this Storekey in my report. I cannot find a way to overcome this. It seems the ranking is only working when the dimensionfield that is used in the relationship with factsales is being used..

Thanks in advance.

[dax]

Hi @Anonymous , 

I am not clear about your data structure, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[parry2k]

@Anonymous can you share how your tables are related? And may be post sample datA?