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

Dynamic Customer Grouping

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_keycustomer_keycompany_keysale_amountdate_key
123A155 
234B250 
567C390 
891B256 
987D113 


How can I achieve this in Power BI?

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

wdx223_Daniel
Super User
Super User

@spacerocket22  please try this step by step

Step 1: create a date table

wdx223_Daniel_0-1605080557434.png

Step 2: create a dimension table

wdx223_Daniel_1-1605080646028.png

 

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.

wdx223_Daniel_2-1605080758759.png

 

Step 4: create a measure

wdx223_Daniel_3-1605080801781.png

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

wdx223_Daniel_4-1605080878100.png

 

that's all.

 

View solution in original post

9 REPLIES 9
wdx223_Daniel
Super User
Super User

@spacerocket22  please try this step by step

Step 1: create a date table

wdx223_Daniel_0-1605080557434.png

Step 2: create a dimension table

wdx223_Daniel_1-1605080646028.png

 

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.

wdx223_Daniel_2-1605080758759.png

 

Step 4: create a measure

wdx223_Daniel_3-1605080801781.png

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

wdx223_Daniel_4-1605080878100.png

 

that's all.

 

@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_0-1605082484685.png

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

@wdx223_Daniel 

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

Something like this:

spacerocket22_0-1605083829700.png

 

@spacerocket22 

can create a dimension table like this. it is a hard work wdx223_Daniel_0-1605084643089.png

amitchandak
Super User
Super User

@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

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

@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
Super User
Super User

@spacerocket22 you need provide more sample data and with date

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