Forum Discussion
Rankx problem with column linked being used
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:
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.
2 Replies
- parry2k
Super User
Anonymous can you share how your tables are related? And may be post sample datA?
daxCommunity Support
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
