Dynamic Customer Grouping

[spacerocket22]

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?

[amitchandak · Accepted solution]

@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

[wdx223_Daniel · Accepted solution]

@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.

[spacerocket22]

@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?

[wdx223_Daniel]

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

[spacerocket22]

@wdx223_Daniel 

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

Something like this:

[wdx223_Daniel]

@spacerocket22 

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

[amitchandak · Accepted solution]

@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

[spacerocket22]

@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?

[spacerocket22]

@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

[wdx223_Daniel]

@spacerocket22 you need provide more sample data and with date