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.
Hello!
I need help segmenting customers by groups of 10 according the sales (Desc).
I rank all my customers in groups of 10 customers each ( according to sales), so when I select a group of 10 customers I only want to see the results for that 10 customers.
I created this table:
Group Sales Min Max
Top 10 | 0 | 10 | |
Top [11 - 20] | 10 | 20 | |
Top [21 - 30] | 20 | 30 | |
Top [31 - 40] | 30 | 40 | |
Top [41 - 50] | 40 | 50 | |
Not Top 50 | 100 | 10000 |
I am trying to do the cohort analasys for only that 10 customers, comparing the first buy and the next one, and relating it to period of purchase.
The result of my analysis is the Retention Pediod, the measure is:
I already created the cohort analysis, but only works for all customers, and it can't be filter by the Group of 10 customers.
So I think the error on the 2nd line of the DAX measure above
VAR CustomerDimension = Values( SALES [customer_name]) => its not filtering only for 10 customers
Can u please help?
Thanks
Telmo
Hi @Anonymous,
Please create an extra measure:
Measure1 = VAR sumsales = CALCULATE ( SUM ( SALES[sales] ), ALLEXCEPT ( SALES, SALES[customer_name] ) ) RETURN IF ( sumsales >= SELECTEDVALUE ( GroupCustomer[Min] ) && sumsales < SELECTEDVALUE ( GroupCustomer[Max] ), 1, 0 )
Then, modify the formula for [Retention Period] as below.
Retention Period = VAR CustomerDimension = FILTER ( VALUES ( SALES[customer_name] ), [Measure1] = 1 ) RETURN IF ( SELECTEDVALUE ( 'Cohort Periods'[Period] ) = 0, [UNIQUE CUSTOMERS], CALCULATE ( [UNIQUE CUSTOMERS], FILTER ( CustomerDimension, COUNTROWS ( FILTER ( 'Cohort Periods', [Retention Days] > 'Cohort Periods'[Min Days] && [Retention Days] <= 'Cohort Periods'[Max Days] ) ) > 0 ) ) )
Regards,
Yuliana Gu
Hello v-yulgu-msft !
Thank u for trying to help.
It doesn´t work however, because when I am selectig the group ( for example TOP 10 customers) it doesnt filter only those 10 customers.
Maybe the new measure1 = has to be related to the rank oof the customer and not the customer name?
Can u also explain why u used this ALLEXCEPT ( SALES, SALES[customer_name] ) )
thank u !
If possible could I sent u by private msg the file with my data and my model wtih masked sensible data of course?
Telmo
Hi @Anonymous ,
For better understanding about ALLEXCEPT, please have a look at this blog.
Since you have created a sample file with masked sensible data, you could share it via OneDrive and paste the download link here. Remeber to show us your desired output.
Regards,
Yuliana Gu
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |