topic Re: Dynamic Customer Grouping in DAX Commands and Tips

Dynamic Customer Grouping

spacerocket22 — Wed, 11 Nov 2020 05:32:33 GMT

I work for an organization that owns 15 companies.
1. I want to show the number of customers that purchased from 1 company, 2 companies, 3 companies, etc.
2. I also want to know the number of customers for different combinations of these companies. (Cross company customers)
3. I want this to be dynamic so when the user selects a date range on the slicer, everything updates accordingly.

A simple example of data I have:

transaction_key	customer_key	company_key	sale_amount	date_key
123	A	1	55
234	B	2	50
567	C	3	90
891	B	2	56
987	D	1	13

How can I achieve this in Power BI?

Re: Dynamic Customer Grouping

wdx223_Daniel — Wed, 11 Nov 2020 05:48:06 GMT

spacerocket22 you need provide more sample data and with date

Re: Dynamic Customer Grouping

spacerocket22 — Wed, 11 Nov 2020 06:01:03 GMT

wdx223_Daniel
Thank you for this.
Attaching a sample data file with 70 transactions, 30 customers, and 7 companies.

https://docs.google.com/spreadsheets/d/1aZSttC8ne2xzWg1H_LFCIsFEWr_pjSmq5WGpdl8P_d0/edit?usp=sharing

Re: Dynamic Customer Grouping

amitchandak — Wed, 11 Nov 2020 06:02:22 GMT

spacerocket22 , You have to create a measure

count(Table[company_key]), As a measure, this can not be used as a filter. You need to create an independent table manually or using generateseries with 1 to 100(say). And create new measure/s that uses this table.

Refer to my dynamic segementation video. You case = join will work no need of range unless you need a range

https://www.youtube.com/watch?v=CuczXPj0N-k

Re: Dynamic Customer Grouping

spacerocket22 — Wed, 11 Nov 2020 07:46:50 GMT

amitchandak Great video. Thanks a lot for this!!
This solves my first question, yes. I can know the number of customers with respect to the number of companies they transact in, but I cannot know the company combinations using this. Given that there are 15 companies, the combinations are a lot.
I can use the same method to get combinations (manually creating a table with possible combinations) but that might be a lot of manual work and measures. Any other solutions for finding combination wise customers?

Re: Dynamic Customer Grouping

wdx223_Daniel — Wed, 11 Nov 2020 07:48:24 GMT

spacerocket22 please try this step by step

Step 1: create a date table

Step 2: create a dimension table

Step 3: create relationship between Dates[Date] and FactTable[date_key]. of cause, you need convert your data in column of date_key to Date Type.

Step 4: create a measure

Step 5: draw the column and measure into a tabulor visual, and date to slicer

that's all.

Re: Dynamic Customer Grouping

spacerocket22 — Wed, 11 Nov 2020 07:59:34 GMT

wdx223_Daniel Amazingg. This workss!!
Any idea on how we can achieve part 2 I mentioned?
Like finding the exact company pairs with respect to number of customers?
For ex:
number of customers that buy from company 1 and company 2?
number of customers that buy from company 1 and company 2 and company 3?
And all possible combinations?

Re: Dynamic Customer Grouping

wdx223_Daniel — Wed, 11 Nov 2020 08:15:45 GMT

spacerocket22 might this work, draw company_key in a list slicer, then you can assign your combination.

Re: Dynamic Customer Grouping

spacerocket22 — Wed, 11 Nov 2020 08:37:20 GMT

wdx223_Daniel

I wish to list the company names as combinations as well.
Any ideas?

Something like this:

Re: Dynamic Customer Grouping

wdx223_Daniel — Wed, 11 Nov 2020 08:54:11 GMT

spacerocket22

can create a dimension table like this. it is a hard work