Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

@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

Anonymous
Not applicable

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.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

 

Anonymous
Not applicable

Hey @Anonymous can you share the PBIX with me?

Anonymous
Not applicable

AlB
Super User
Super User

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 

SU18_powerbi_badge

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.