topic Dynamic combined customer and product 8020 analysis in DAX Commands and Tips

Dynamic combined customer and product 8020 analysis

Anonymous — Mon, 14 Nov 2022 18:19:54 GMT

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?

Re: Dynamic combined customer and product 8020 analysis

amitchandak — Tue, 15 Nov 2022 01:56:37 GMT

Anonymous , Not very clear. Are you looking for

Market Basket Analysis
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/
https://www.mssqltips.com/sqlservertip/5428/market-basket-analysis-in-r-and-power-bi/

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk

Re: Dynamic combined customer and product 8020 analysis

Anonymous — Tue, 15 Nov 2022 02:37:26 GMT

Hi amitchandak

Let me explain with tables from excel...

I have customer and product data:

Customer	Product	Sales
Customer 1	Product 1	105,000
Customer 1	Product 2	58,995
Customer 1	Product 3	141,138
Customer 1	Product 4	49,331
Customer 1	Product 5	55,037
Customer 2	Product 1	35,000
Customer 2	Product 2	16,000
Customer 2	Product 3	2,000
Customer 2	Product 4	1,000
Customer 2	Product 5	3,041
Customer 3	Product 1	20,000
Customer 3	Product 2	57,115
Customer 3	Product 3	21,307
Customer 3	Product 4	75,011
Customer 3	Product 5	5,579
Customer 4	Product 1	44,000
Customer 4	Product 2	11,944
Customer 4	Product 3	3,102
Customer 4	Product 4	2,468
Customer 4	Product 5	10,279
Customer 5	Product 1	120,000
Customer 5	Product 2	6,061
Customer 5	Product 3	2,192
Customer 5	Product 4	1,168
Customer 5	Product 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 summary	Sales summary	Cummul %	A/B
Customer 1	409,501	48%	A
Customer 3	179,011	69%	A
Customer 5	136,199	85%	B
Customer 2	71,793	93%	B
Customer 4	57,041	100%	B
Total	853,545

I do the same with Product:

Product summary	Sales summary	Cummul %	A/B
Product 1	324,000	38%	A
Product 2	169,739	58%	A
Product 3	150,115	75%	A
Product 4	128,977	91%	B
Product 5	80,714	100%	B
Total	853,545

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

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

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
		A	B	Total
Customer	A	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?

Re: Dynamic combined customer and product 8020 analysis

Anonymous — Sun, 20 Nov 2022 21:04:31 GMT

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

Many thanks, Mark

Re: Dynamic combined customer and product 8020 analysis

Ashish_Mathur — Wed, 23 Nov 2022 05:56:45 GMT

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?

Re: Dynamic combined customer and product 8020 analysis

Anonymous — Wed, 23 Nov 2022 06:20:50 GMT

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.

Re: Dynamic combined customer and product 8020 analysis

Ashish_Mathur — Wed, 23 Nov 2022 12:17:56 GMT

Hi,

You may download my PBI file from here.

Hope this helps.

Re: Dynamic combined customer and product 8020 analysis

Anonymous — Wed, 23 Nov 2022 20:25:04 GMT

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
		A	B	Total
Customer	A	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?

Re: Dynamic combined customer and product 8020 analysis

Ashish_Mathur — Sat, 26 Nov 2022 04:18:29 GMT

Hi,

I tried but could not solve the question.

Re: Dynamic combined customer and product 8020 analysis

Anonymous — Sun, 27 Nov 2022 19:17:21 GMT

No problem Ashish_Mathur , thank you for trying!

Re: Dynamic combined customer and product 8020 analysis

v-jianboli-msft — Mon, 28 Nov 2022 06:49:16 GMT

Hi Anonymous ,

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:

Then create a matrix visual:

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.

Re: Dynamic combined customer and product 8020 analysis

Anonymous — Tue, 29 Nov 2022 01:56:53 GMT

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

Re: Dynamic combined customer and product 8020 analysis

PeterNicholson — Thu, 21 Mar 2024 09:43:06 GMT

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