cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeromeTEL
Regular 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

9 REPLIES 9
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?






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.






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/
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.

 

 

 

 






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
Super User
Super User

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 beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!