Forum Discussion
Help with formula for multiple criteria
Hi!
I am pretty new in power-bi and need help with a formula and logic for a customer analysis. Since I couldn't find any similar issue in the forum I try to explain my situation below.
The company I work for run 3 different brands, lets call them lens1, lens2, and lens3. What I now need to get a grip of is (1) how many customers as we have in each brand (witin the same market), (2) how many customers that belongs to more than one brand, and also (3) whithin which brand the latest purchase took place.
Sample data is attached below:
Sales table
| Brand_key | market_key | customer_key | order_number | order_date | latest_order_date |
| 1 | 11 | 123 | 1001 | 09-jan-18 | 10-dec-19 |
| 1 | 11 | 123 | 1020 | 10-dec-19 | 10-dec-19 |
| 2 | 11 | 123 | 1100 | 05-jan-17 | 05-jan-17 |
| 3 | 11 | 123 | 1200 | 20-jun-18 | 20-jun-18 |
| 1 | 14 | 125 | 1201 | 11-jan-20 | 11-jan-20 |
| 1 | 14 | 126 | 1202 | 12-jan-20 | 12-jan-20 |
| 2 | 14 | 125 | 1205 | 02-feb-20 | 02-feb-20 |
| 3 | 14 | 127 | 1206 | 10-mar-20 | 10-mar-20 |
| 1 | 12 | 128 | 1207 | 10-mar-20 | 10-mar-20 |
| 2 | 12 | 129 | 1208 | 10-jun-20 | 10-jun-20 |
| 3 | 12 | 129 | 1209 | 10-jul-20 | 10-jul-20 |
| 1 | 14 | 130 | 1210 | 10-aug-20 | 10-aug-20 |
| 2 | 14 | 130 | 1211 | 11-aug-20 | 11-aug-20 |
| 1 | 14 | 131 | 1212 | 12-aug-20 | 12-aug-20 |
| 2 | 12 | 132 | 1213 | 10-jul-20 | 10-jul-20 |
| 1 | 13 | 133 | 1005 | 10-aug-17 | 10-jun-20 |
| 1 | 13 | 133 | 1215 | 10-jun-20 | 10-jun-20 |
| 2 | 13 | 133 | 1220 | 05-jun-19 | 05-jun-19 |
| 3 | 13 | 133 | 1225 | 11-mar-19 | 11-mar-19 |
Brand table
| Brand_key | Brand_name |
| 1 | lens1 |
| 2 | lens2 |
| 3 | lens3 |
Market table
| Market_key | Market_NAME |
| 11 | FINLAND |
| 12 | DENMARK |
| 13 | NORWAY |
| 14 | SWEDEN |
Customer table
| CUSTOMER_KEY | CUSTOMER_EMAIL |
| 123 | [email protected] |
| 125 | [email protected] |
| 126 | [email protected] |
| 127 | [email protected] |
| 128 | [email protected] |
| 129 | [email protected] |
| 130 | [email protected] |
| 131 | [email protected] |
| 132 | [email protected] |
| 133 | [email protected] |
To be mention is that last order date within the sales table is a calculated column based brand, market, and customer key. The latest order date will always be max.
Expected output from sample data:
Total no customers in lens 1 | 7 |
Total no customers in lens 2 | 6 |
Total no customers in lens 3 | 4 |
Customers purchased in both lens1&lens2 (in same market) | 4 |
Customers purchased in both lens1&lens3 (in same market) | 2 |
Customers purchased in both lens2&lens3 (in same market) | 3 |
Customer purchased in lens1&lens2 with latest purchase in lens1 (in same market) | 2 |
Customer purchased in lens2&lens3 with latest purchase in lens2 (in same market) | 1 |
Customer purchased in lens1&lens3 with latest purchase in lens1 (in same market) | 2 |
Explanation in words
The following three questions need to be answered:
(1) What is the total number of customers for each brand?
First I need to understand how many customers I actually have for each brand. The brand is here called lens1, lens2, lens3.
(2) How many customers belongs to more than one brand?
When I know the answer of (1), I need to undertand how many customers that are customers/ have purchased from another brand as well. For example, no customer in lens1 & lens2 are in this sample data 4.
(3) Within which took the latest purchase place?
When knowing which customers that belongs to more than one brand (2), I need to undertand where the latest purchase took place. For example, customers in both lens1 &lens2 where the latest purchase was in lens1 is in the sample data 2.
Hope that some of you can solve this - Thanks in advance!
3 Replies
Anonymous ,Can you share sample data and sample output in a table format?
It should be very similar to churn, in place of time use brand
https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196
https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
https://www.sqlbi.com/articles/computing-new-customers-in-dax/Anonymous - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.hi Anonymous
and 'Previous_Brand'[BRAND_NAME] is a duplicated tables with same dimensions as'Brand'[BRAND_NAME].
why you need two tables for two brand? you'd better keep it one table.
Sample PBIX file and expected output would help tremendously.
Regards,
Lin
