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.
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?
I stumbled across this as I need to do the exact same thing as @Mark_H - was this ever resolved?
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:
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.
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
@Mark_H , 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/
or
And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk
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?
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?
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.
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?
Hi,
I tried but could not solve the question.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |