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
JeromeTEL
Frequent Visitor

Power BI How many clients will visit another outlet?

I need to know how many clients who made their first purchase in outlet "A" will visit other outlets instead of only going back to outlet A. 

 

I assume I need a measure that would filter only clients who have made their first transaction in "A".

And then out of these clients, how many of them out make subsequent transactions in other outlets. 

For clients who visit A + another outlet, I will count them. 

For clients who visit A and only A again, I will ignore them.

 

Excel file for sample data

 

Thank you.

1 ACCEPTED SOLUTION

Hi,

Would you be OK with this result?  The matrix shows you how many outlets where visited by each client and how of those clients visited other outlets as well.  So, 1,038 customers visited outlet C and of those 117 also visited outlet B.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
Syndicate_Admin
Administrator
Administrator

That's great

However, it doesn't solve what I'm looking for, it's similar to that cohort analysis, but it has to be not by date but by point of sale (Outlet)

How many from point of sale A have visited other points of sale


Hi,

Please study the solution carefully and then try to apply the same concept to your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Syndicate_Admin
Administrator
Administrator

Hi Friend, checking the forum, very good result, I would like if you could share the file again, it would be very helpful I am looking to do the same thing you did in that array

parry2k
Super User
Super User

@JeromeTEL ok, is the same logic on the new transaction table? what is the outlet column in this new table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes  @parry2k it is the same logic, the outlet column is 'boutique'.

You may use any value under boutique for an example.

 

Many thanks

parry2k
Super User
Super User

@JeromeTEL did you check the solution that is already posted?

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ashish_Mathur
Super User
Super User

Hi,

Do you want to know how many or do you also want to know which other outlets have they visited?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur . I would only need to know how many. I do not need to know which other outlets they visited. Thank you!

Hi,

Would you be OK with this result?  The matrix shows you how many outlets where visited by each client and how of those clients visited other outlets as well.  So, 1,038 customers visited outlet C and of those 117 also visited outlet B.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for sharing such a brilliant response, could you please share the file again, thank you.

Hi @Ashish_Mathur,

 

Thank you for sharing such a brilliant response, could you please share the file again, thank you.

You are welcome.  I do not have the file.  Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, thank you.

 

Andrs135_6-1698878309576.png

 

I would like to achieve this result with my sales outlets - "The matrix shows how many customers visited each sales outlet and how many of these customers also visited other sales outlets. Therefore, 1,038 customers visited sales outlet C and of those, 117 also visited sales outlet B."

 

I am attaching my Excel table.

It was amazing how you reached that result, and I've been working on it for days without success. Thank you for your help.

 

Hi Ashish, thank you.

 

Andrs135_6-1698878309576.png

 

I would like to achieve this result with my sales outlets - "The matrix shows how many customers visited each sales outlet and how many of these customers also visited other sales outlets. Therefore, 1,038 customers visited sales outlet C and of those, 117 also visited sales outlet B."


I am attaching my Excel table.

It was amazing how you reached that result, and I've been working on it for days without success. Thank you for your help.

 

Hi,

Similar problem solved in the attached file.

Hope this helps.

Ashish_Mathur_0-1698881506040.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Genial,

Sin embargo no resuelve lo que estoy buscando, es parecido a ese analisis cohorte, pero tiene que ser no por fecha sino por punto de venta ( Outlet) 

Cuantos del punto de venta A han visitado otros puntos de ventas 

parry2k
Super User
Super User

@JeromeTEL I'm not sure if @CNENFRNL solution is the answer because it will not check if the first visit is on the selected outlet, in your case you are checking for outlet "A", or maybe I misunderstood your requirement.

 

Here is what I think you are looking for:

 

Client Meets Criteria = 
VAR __baseOutlet = "A"
VAR __baseRankTable =
ADDCOLUMNS(
    CALCULATETABLE ( Sheet1 ) , "@rank",
    RANKX ( 
        FILTER( 
            ALL ( Sheet1 ),  
            Sheet1[Client ID] = MAX ( Sheet1[Client ID] )  
        ), 
        CALCULATE (  MIN ( Sheet1[Transaction Date] ) ), , ASC 
    ) 
) 
//filter out client where there first purchase is at Outlet = "A"
VAR __filterBaseTableForOutlet = FILTER ( __baseRankTable, [Outlet] = __baseOutlet && [@rank] = 1 ) 

//find out outlet that not Base Outlet i.e. "A" and the sales is after the first transactions date
VAR __tableWithOtherSales = 
    ADDCOLUMNS ( 
        __filterBaseTableForOutlet, 
        "@Other Outlet Counts", 
        VAR __firstDate = [Transaction Date] 
        RETURN 
            CALCULATE ( 
                COUNTROWS ( Sheet1 ), 
                Sheet1[Outlet] <> __baseOutlet, 
                Sheet1[Transaction Date] > __firstDate 
            ) 
        ) 
//filter table which doesn't have others sales after the first outlet sales
VAR __filertableWithOtherSales = FILTER ( __tableWithOtherSales, NOT ISBLANK ( [@Other Outlet Counts] ) ) 
  
//get list of clients that meet criteria
VAR __count = COUNTROWS ( __filertableWithOtherSales )
RETURN __count 

 

and here are various use cases that meet and don't meet the conditions your described:

 

parry2k_0-1642970814829.png

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k Thank you for your response, it worked. However when I tried the same formula on another table I couldnt get it to work. Would you be able to show me another example with the new table I have added? 

 

Transaction Table 

 

Thank you!

CNENFRNL
Community Champion
Community Champion

CNENFRNL_1-1642965902760.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.