cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help with solution 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_keymarket_keycustomer_keyorder_numberorder_datelatest_order_date
111123100109-jan-1810-dec-19
111123102010-dec-1910-dec-19
211123110005-jan-1705-jan-17
311123120020-jun-1820-jun-18
114125120111-jan-2011-jan-20
114126120212-jan-2012-jan-20
214125120502-feb-2002-feb-20
314127120610-mar-2010-mar-20
112128120710-mar-2010-mar-20
212129120810-jun-2010-jun-20
312129120910-jul-2010-jul-20
114130121010-aug-2010-aug-20
214130121111-aug-2011-aug-20
114131121212-aug-2012-aug-20
212132121310-jul-2010-jul-20
113133100510-aug-1710-jun-20
113133121510-jun-2010-jun-20
213133122005-jun-1905-jun-19
313133122511-mar-1911-mar-19

 

Brand table

Brand_keyBrand_name
1lens1
2lens2
3lens3

 

Market table

Market_keyMarket_NAME
11FINLAND
12DENMARK
13NORWAY
14SWEDEN

 

Customer table

CUSTOMER_KEYCUSTOMER_EMAIL
123123@gmail.com
125125@gmail.com
126126@gmail.com
127127@gmail.com
128128@gmail.com
129129@gmail.com
130130@gmail.com
131131@gmail.com
132132@gmail.com
133133@gmail.com

 

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 someone has the an idea of how to to this - Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Help with solution for multiple criteria

Hi @mg_2000 ,

 

How about this?

 

1. Create measures.

 

Customers purchased more brand = 
COUNTROWS (
    FILTER (
        VALUES ( Sales[customer_key] ),
        COUNTROWS (
            INTERSECT (
                CALCULATETABLE ( VALUES ( Sales[Brand_key] ) ),
                VALUES ( Brand[Brand_key] )
            )
        )
            = COUNTROWS ( VALUES ( Brand[Brand_key] ) )
    )
)
Customers purchased more brand 2 = 
VAR MinBrandKey =
    MIN ( Brand[Brand_key] )
VAR t =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                Sales,
                "Last_", CALCULATE (
                    MAX ( Sales[latest_order_date] ),
                    FILTER (
                        ALLSELECTED ( Sales ),
                        Sales[market_key] = MAX ( Sales[market_key] )
                            && Sales[customer_key] = MAX ( Sales[customer_key] )
                    )
                )
            ),
            [Last_] = [latest_order_date]
                && [Brand_key] = MinBrandKey
        ),
        [customer_key]
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            COUNTROWS (
                INTERSECT (
                    CALCULATETABLE ( VALUES ( Sales[Brand_key] ) ),
                    VALUES ( Brand[Brand_key] )
                )
            )
                = COUNTROWS ( VALUES ( Brand[Brand_key] ) )
        )
    )
Customers purchased more brand and latest order date in min selected brand =
SUMX ( VALUES ( Sales[customer_key] ), [Customers purchased more brand 2] )
Title 1 =
IF (
    COUNTROWS ( VALUES ( Brand ) ) = 1,
    "Total no customers in " & SELECTEDVALUE ( Brand[Brand_name] ),
    "Customers purchased in "
        & CONCATENATEX ( VALUES ( Brand[Brand_name] ), [Brand_name], " & " ) & " (in same market)"
)
Title 2 = 
IF (
    COUNTROWS ( VALUES ( Brand ) ) = 1,
    "Total no customers in " & SELECTEDVALUE ( Brand[Brand_name] ),
    "Customer purchased in "
        & CONCATENATEX ( VALUES ( Brand[Brand_name] ), [Brand_name], " & " ) & " with latest purchase in "
        & MIN ( Brand[Brand_name] ) & " in same market"
)
Total no. customers = DISTINCTCOUNT(Sales[customer_key])

 

 

2. Create visuals.

2.PNG1.PNG

 

3. Test.

multi.gif

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: Help with solution for multiple criteria

@mg_2000 

I worked out a solution but I was not sure about the last question, please check.

You can download the file: HERE

Fowmy_0-1597409225829.png



________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Highlighted
Community Support
Community Support

Re: Help with solution for multiple criteria

Hi @mg_2000 ,

 

How about this?

 

1. Create measures.

 

Customers purchased more brand = 
COUNTROWS (
    FILTER (
        VALUES ( Sales[customer_key] ),
        COUNTROWS (
            INTERSECT (
                CALCULATETABLE ( VALUES ( Sales[Brand_key] ) ),
                VALUES ( Brand[Brand_key] )
            )
        )
            = COUNTROWS ( VALUES ( Brand[Brand_key] ) )
    )
)
Customers purchased more brand 2 = 
VAR MinBrandKey =
    MIN ( Brand[Brand_key] )
VAR t =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                Sales,
                "Last_", CALCULATE (
                    MAX ( Sales[latest_order_date] ),
                    FILTER (
                        ALLSELECTED ( Sales ),
                        Sales[market_key] = MAX ( Sales[market_key] )
                            && Sales[customer_key] = MAX ( Sales[customer_key] )
                    )
                )
            ),
            [Last_] = [latest_order_date]
                && [Brand_key] = MinBrandKey
        ),
        [customer_key]
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            COUNTROWS (
                INTERSECT (
                    CALCULATETABLE ( VALUES ( Sales[Brand_key] ) ),
                    VALUES ( Brand[Brand_key] )
                )
            )
                = COUNTROWS ( VALUES ( Brand[Brand_key] ) )
        )
    )
Customers purchased more brand and latest order date in min selected brand =
SUMX ( VALUES ( Sales[customer_key] ), [Customers purchased more brand 2] )
Title 1 =
IF (
    COUNTROWS ( VALUES ( Brand ) ) = 1,
    "Total no customers in " & SELECTEDVALUE ( Brand[Brand_name] ),
    "Customers purchased in "
        & CONCATENATEX ( VALUES ( Brand[Brand_name] ), [Brand_name], " & " ) & " (in same market)"
)
Title 2 = 
IF (
    COUNTROWS ( VALUES ( Brand ) ) = 1,
    "Total no customers in " & SELECTEDVALUE ( Brand[Brand_name] ),
    "Customer purchased in "
        & CONCATENATEX ( VALUES ( Brand[Brand_name] ), [Brand_name], " & " ) & " with latest purchase in "
        & MIN ( Brand[Brand_name] ) & " in same market"
)
Total no. customers = DISTINCTCOUNT(Sales[customer_key])

 

 

2. Create visuals.

2.PNG1.PNG

 

3. Test.

multi.gif

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors