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

Dynamic combined customer and product 8020 analysis

Hi,

 

Im wanting to create a dynamic measure to analyse which "A" customers bought which "A" products and so on. Must be dynamic, to use the page filters I have.

 

Analysis to spit out:

 

1) sum of sales for A customer purchasing A product, also AB, BA, BB, etc 

2) number of customer (and products) in AA, AB, etc

3) which customers / products in AA, AB etc

 

Data set is transaction order history, so includes customer name / code, product name / code and sale date.

 

Can someone please help as my DAX is not at this level, assume it needs to be done in virtual tables then bringing together?

12 REPLIES 12
PeterNicholson
Frequent Visitor

I stumbled across this as I need to do the exact same thing as @Mark_H - was this ever resolved?

 

v-jianboli-msft
Community Support
Community Support

Hi @Mark_H ,

 

Please try:

Add two columns:

AB_cust =
VAR _a =
    SUMMARIZE (
        'Data',
        [Customer],
        "Sales summary",
            CALCULATE (
                SUM ( Data[Sales] ),
                FILTER ( 'Data', [Customer] = EARLIER ( Data[Customer] ) )
            )
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "Cummul %",
            DIVIDE (
                SUMX (
                    FILTER ( _a, [Sales summary] >= EARLIER ( [Sales summary] ) ),
                    [Sales summary]
                ),
                SUMX ( _a, [Sales summary] )
            )
    )
VAR _c =
    ADDCOLUMNS ( _b, "A/B", IF ( [Cummul %] < 0.8, "A", "B" ) )
RETURN
    MAXX ( FILTER ( _c, [Customer] = EARLIER ( Data[Customer] ) ), [A/B] )


AB_prod =
VAR _a =
    SUMMARIZE (
        'Data',
        [Product],
        "Sales summary",
            CALCULATE (
                SUM ( Data[Sales] ),
                FILTER ( 'Data', [Product] = EARLIER ( Data[Product] ) )
            )
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "Cummul %",
            DIVIDE (
                SUMX (
                    FILTER ( _a, [Sales summary] >= EARLIER ( [Sales summary] ) ),
                    [Sales summary]
                ),
                SUMX ( _a, [Sales summary] )
            )
    )
VAR _c =
    ADDCOLUMNS ( _b, "A/B", IF ( [Cummul %] < 0.8, "A", "B" ) )
RETURN
    MAXX ( FILTER ( _c, [Product] = EARLIER ( Data[Product] ) ), [A/B] )

Output:

vjianbolimsft_0-1669618102618.png

Then create a matrix visual:

vjianbolimsft_1-1669618126115.png

Best Regards,

Jianbo Li

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

Hi @v-jianboli-msft ,

 

Unfortunately this assigns the A/B globally (static).

 

What I am looking for is a dynamic A/B classification, i.e. the ability to assign A/B based on the page level filter I use.

 

Can this be done?

 

Mark

Hi @amitchandak 

 

Let me explain with tables from excel...

 

I have customer and product data:

CustomerProductSales
Customer 1Product 1         105,000
Customer 1Product 2           58,995
Customer 1Product 3         141,138
Customer 1Product 4           49,331
Customer 1Product 5           55,037
Customer 2Product 1           35,000
Customer 2Product 2           16,000
Customer 2Product 3             2,000
Customer 2Product 4             1,000
Customer 2Product 5             3,041
Customer 3Product 1           20,000
Customer 3Product 2           57,115
Customer 3Product 3           21,307
Customer 3Product 4           75,011
Customer 3Product 5             5,579
Customer 4Product 1           44,000
Customer 4Product 2           11,944
Customer 4Product 3             3,102
Customer 4Product 4             2,468
Customer 4Product 5           10,279
Customer 5Product 1         120,000
Customer 5Product 2             6,061
Customer 5Product 3             2,192
Customer 5Product 4             1,168
Customer 5Product 5             6,778

 

If I summarise by Customer, I can categorise which ones contribute to >80% of sales, and call them "A", otherwise "B":

Customer summarySales summary Cummul % A/B
Customer 1                    409,50148%A
Customer 3                    179,01169%A
Customer 5                    136,19985%B
Customer 2                      71,79393%B
Customer 4                      57,041100%B
Total                    853,545  

 

I do the same with Product:

Product summarySales summary Cummul % A/B
Product 1                    324,00038%A
Product 2                    169,73958%A
Product 3                    150,11575%A
Product 4                    128,97791%B
Product 5                      80,714100%B
Total                    853,545  

 

I can then go back to the original table and populate an A/B customer and A/B product column:

CustomerProductSalesAB_custAB_prod
Customer 1Product 1         105,000AA
Customer 1Product 2           58,995AA
Customer 1Product 3         141,138AA
Customer 1Product 4           49,331AB
Customer 1Product 5           55,037AB
Customer 2Product 1           35,000BA
Customer 2Product 2           16,000BA
Customer 2Product 3             2,000BA
Customer 2Product 4             1,000BB
Customer 2Product 5             3,041BB
Customer 3Product 1           20,000AA
Customer 3Product 2           57,115AA
Customer 3Product 3           21,307AA
Customer 3Product 4           75,011AB
Customer 3Product 5             5,579AB
Customer 4Product 1           44,000BA
Customer 4Product 2           11,944BA
Customer 4Product 3             3,102BA
Customer 4Product 4             2,468BB
Customer 4Product 5           10,279BB
Customer 5Product 1         120,000BA
Customer 5Product 2             6,061BA
Customer 5Product 3             2,192BA
Customer 5Product 4             1,168BB
Customer 5Product 5             6,778BB

 

After this, I can create a view (in this case I did a quick excel pivot) to obtain a quadrant of A/B customer purchasing A/B product:

  Product  
  ABTotal
CustomerA                    403,555         184,957         588,512
 B                    240,299           24,734         265,033
 Total                    643,854         209,691         853,545

 

I want to keep the A/B classification dynamic as will use my page filters to slice and dice my desired view(s).

 

Does this help?

Mark_H
Frequent Visitor

Hi @amitchandak & @Ashish_Mathur , let me know if you are interested.

 

Many thanks, Mark

Hi,

I think i can dynamically generate 2 tables as the final result - one for Products and another for Customers.  Would you be interested in that?


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

Hi Ashish, wonderful!

 

Yes that's sounds about right.


The desired output is combining the customer and product tables, then filtering for only 'A' customers and 'A' products (or the other combos, 'A' and 'B', 'B' and 'A' or 'B' and 'B').

 

I then want to return $ sales, or # customers or # products in those filter combos.

Hi,

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/

Hi Ashish, very close...

 

The total value is the right result, what I'm looking for is crossing 'customer' and 'product' to find the detail for when an 'A customer' buys an 'A product' and so on. See below, bold highlights the measures you created work for the totals, I'm wanting to cross the two filters to get the detail in between.

 

  Product  
  ABTotal
CustomerA                    403,555         184,957         588,512
 B                    240,299           24,734         265,033
 Total                    643,854         209,691         853,545

 

Thing this can be done?

Hi,

I tried but could not solve the question.


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

No problem @Ashish_Mathur , thank you for trying!

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.

Top Solution Authors