I have two tables. The First holds client info and the second is sales data.
I want to rank the clients based on sales.
The client data includes clients that are active and inactive and belong to different areas of the business. This info is needed for other visuals.
But for the ranking, I want to only include clients that are active and belong to one area of the business. Additionally, I want the user to be able to select from the list of clients and this will show a card that had the rank number of that client among all the clients that are active and belong to their area of the business.
If I use RANKX(ALL('Clients'),CALCULATE( SUM('Sales')))
I get a ranking that includes active and inactive clients, and clients from all areas of the business.
I am open to suggestions on how to achieve what I need. However, the following is how I tried to achieve it myself.
I created a new table off the Clients table (ClientsB) that filtered out all the inactive clients and only included the clients in the area of the business I am looking for.
I used the following to do this;
ClientsB =
CALCULATETABLE('Clients'
,'Clients'[Status]="Active"
,'Clients'[Area]="Custom"
)
To rank these, I then this DAX RANKX(ALL('ClientsB'),CALCULATE( SUM('Sales')))
This worked, untill I selected a value in a slicer.
The moment I seelct a Client from a slicer it re-ranked the data and set the client to be ranked number 1, when in fact they we say number 9 when the data is not filtered.
I thought that the ALL function would always include all data from the table being ranked. Is this not right?
I am guessing I am having this issue because it is a table I created as I do not have this issue with the original table.
Thanks for any assistance you can give.
Cheers,
