cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

How to calculate the Store number 1 in sales for each client in my organization?

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:

 

Rank Store ID = RANKX(ALL(orders[store_id]),RANKX(ALL(orders[store_id]),CALCULATE([Num orders]))+DIVIDE(RANKX(ALL(orders[store_id]),CALCULATE([revenue])),(COUNTROWS(ALL(orders[store_id]))+1)),,ASC)
 
This gets me the ranking of all store ID, calculating first the number of orders and also the total revenue, just to break ties if a client has the same amount of orders with 2 or more stores.
 
After this I created another measure to obtain the Store ID when this rank is equal 1:
Store ID Rank 1 = CALCULATE(ALL(orders[store_id]),FILTER(ALLSELECTED(orders[store_id]),[Rank Store ID]=1))
 
So after that I would like to use a calculate column using group by with all my Clientes ID and calculate the Store ID Rank 1 for each cliente, but intead of the Store ID I would like to get the Store Name, so I tried to use LOOKUPVALUE in a table like this:
 
Info Clients = ADDCOLUMNS(SUMMARIZE(Clients,Clients[id]), "Store Name Rank 1", LOOKUPVALUE(store[name],store[ID], [Store ID Rank 1]))
 
But since I have in my data more than 50 thousands Clientes ID, everytime I try to calculate, the power BI returns me that I don't have enough memory to perform this.
 
I would like to know if there is another way more efficient to calculate this, this parameter is very important to our organization
Thank you very much in advance!!
1 ACCEPTED SOLUTION

Hey,

 

PFA the solution.

 

Create an additional table = 

Rank = SUMMARIZE(Orders,Orders[client_id],Orders[store_id],"Orders",COUNT(Orders[store_id]))
 
Post this add a calculate column to this table = 
Star = RANKX(FILTER('Rank','Rank'[client_id]=EARLIER('Rank'[client_id])),'Rank'[Orders],,DESC)
 
This will rank stores per client, post this, go to the canvas page drag "Star" in the filter pane and select is equal to 1, drag your client ID and Store ID, you will get groups of clients where store ID=1.
 
If you like the solution, kindly, accept it and let me know.

View solution in original post

12 REPLIES 12
Resolver IV
Resolver IV

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.

Resolver IV
Resolver IV

Sure, I will look at the file tomorrow, in the meantime have you tried the solution?

@nishantchawla 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 = 

Rank = SUMMARIZE(Orders,Orders[client_id],Orders[store_id],"Orders",COUNT(Orders[store_id]))
 
Post this add a calculate column to this table = 
Star = RANKX(FILTER('Rank','Rank'[client_id]=EARLIER('Rank'[client_id])),'Rank'[Orders],,DESC)
 
This will rank stores per client, post this, go to the canvas page drag "Star" in the filter pane and select is equal to 1, drag your client ID and Store ID, you will get groups of clients where store ID=1.
 
If you like the solution, kindly, accept it and let me know.

View solution in original post

@nishantchawla 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?

Resolver IV
Resolver IV

Hi @danlindoso11 

 

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 @nishantchawla 

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 @danlindoso11 can you share the PBIX with me?

Super User III
Super User III

Hi @danlindoso11 

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 

SU18_powerbi_badge

 

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors