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.
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.
Solved! Go to 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.
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.
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
@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
@JeromeTEL did you check the solution that is already posted?
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,
Do you want to know how many or do you also want to know which other outlets have they visited?
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.
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.
Hi Ashish, thank you.
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.
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.
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
@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:
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?
Thank you!
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! |
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |