Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone!
I am having a lot of troubles when it comes to obtain the Store number 1 in sales for each one of my clients calculated in a column.
First of all I created the ranking with the formula RANX as a measure like this:
Solved! Go to Solution.
Hey,
PFA the solution.
Create an additional table =
I think the best solution would be to rank on 1 variable, instead of count or orders you can create a column in the summarize function wherein you can sum up orders+Revenue and that column can be called as "All Total" and then replace the order variable with this newly created "All Total" in the rank function which will rank the numbers as a whole, which would actually tell you the story.
Sure, I will look at the file tomorrow, in the meantime have you tried the solution?
@Anonymous yes I tried but it did not work as well
The main is goal is to set up group by for each client_ID, I can get the rank 1 store. And then I can filter to see all the clients_id in to groups with the same Store rank 1
Hey,
PFA the solution.
Create an additional table =
@Anonymous This is a great solution!! Did not need lots of memory to process, but I only have one more question so this can become perfect
Using this rank system that you said between the amount of orders, if there is two or more stores with the same amount of orders, they will be tied with the same rank.
I would like to break the tie usind the revenue for each store, maybe Sum(order[value]) will attend. But how can I break this tie with the DAX Rankx?
Hi @Anonymous
I dont know why are you creating a calculated column, just created a measure as below
Calculate(selectedvalue(storeID),topn(1,allselected(storeID),Sum(orders),desc)
This measure will give you the ranked 1 storeID
Hey @Anonymous
I tried this measure right now but it did not solve my goal.
I wanted to create a calculed column because I need to get among all the stores that each one of our clients have made, which one is the one that he made the most amount of orders. So if that I can filter all the clients that has the story X (for example) as their rank 1 orders Store, and with that I can personalize my communication with this group.
And a measure I can not do that
You can also try creating a simple dax measure as = var a =distinctcount(orders) return Rankx(allselected(storeID,a,,desc) and in the filter pane drag this measure and always keep it as equal to 1. Post this, make a slicer with your client ID and select the clients and accordingly the store ranking will change and we will come to know that for these many clients this is the no.1 store and likewise.
Did this solve?
Hey @Anonymous can you share the PBIX with me?
hey @Anonymous and @AlB here you go the file
https://drive.google.com/file/d/1OUeXi-i46P1cLi1ZGhIt-4mxkOHLU18p/view?usp=sharing
Hi @Anonymous
Can you show a sample of the relevant tables in your model (3 it seems) and detail their relationships? Or, if possible, share the pbix
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hey @AlB
I can explain you from here, but please tell me if you understand, if not I can send you a print.
The table ORDERS has a column named Store ID (which means the store ID that the client made the order) and this column is linked with the table STORE that has a column named ID.
Also I have a table CLIENTS that has a column named ID and I created a relationship with the table ORDERS that has a column named Clients ID.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |