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.
I would really appreciate some help with a problem I have very frustratingly failed to solve
I have a file that contains a number of customers per period. Some buy just once, some buy multiple times.
I want to know how many customers remain in the subsequent period
ex.
Period 1 = 24 customers
Period 2 = 3 of 24 customers
i want to figure out how to get 3 in period 2
Solved! Go to Solution.
Hi @waterphil,
Then update the measure as below.
Measure = CALCULATE(DISTINCTCOUNT(vw_busdev_report_revised[Name]),FILTER(vw_busdev_report_revised,[Measure 2]=1))
Hi @waterphil,
To create a measure as below. If it doesn't meet your requirement, kindly share your excepted result to me.
Distributors from previous Period = var No = MAX(Table1[Period No]) var per = CALCULATE(SUM(Table1[Recruits]),FILTER(ALL(Table1),Table1[Period No]=No-1)) return IF(ISBLANK(per),BLANK(),per-CALCULATE(SUM(Table1[New Distributors])))
For more details, please check the pbix as attached.
Regards,
Frank
Hi Frank,
i think i have to define each column
recuits - are not yet distributors
new distributors - are distributors coming from recruits who do their first sale
i want to how many new distributors will perform again on the next period
out of 399 recruits in Period 1 we were able to convert 188 new distributors
now, im interested to know out of 188 new distributors in Period 1 how many will stay/sell again on Period 2
Thanks!
this is the result I need - out of 5 new customers in P1 - 4 customers sell again in P2
Period | New | Old | Total Customers | Result |
1 | 5 | 13 | 18 | 4 |
2 | 1 | 11 | 12 | 0 |
3 | 3 | 6 | 9 | 0 |
Total | 9 | 18 | 22 | 4 |
Hi @waterphil,
To create the measures as below.
Measure 2 = var no = MAX('Sheet1 (2)'[Periodno]) var t =CALCULATETABLE(VALUES('Sheet1 (2)'[Name]),FILTER(ALL('Sheet1 (2)'),'Sheet1 (2)'[Periodno]<> no )) return IF(SELECTEDVALUE('Sheet1 (2)'[New Customer])=FALSE(),BLANK(),IF(SELECTEDVALUE('Sheet1 (2)'[Name])in t,1,0))
Measure 3 = SUMX('Sheet1 (2)',[Measure 2])
As the data you shared, the result should be like this.
Regards,
Frank
computer got hung if I'm using my actual dataset
tried to remove some rows to test, measure 2 is correct but measure 3 gives me an incorrect value
Thanks!
Hi @waterphil,
Then update the measure as below.
Measure = CALCULATE(DISTINCTCOUNT(vw_busdev_report_revised[Name]),FILTER(vw_busdev_report_revised,[Measure 2]=1))
Hi @waterphil,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Hi Frank,
my computer got hung when I tried to use measure 3 in my actual datasets, measure 2 is working fine
Hi Frank,
I think i have to define each column
Recruits - are not yet distributors until they sell and converted to new distributors
New Distributors - i was able to get the new distributors coming from our recruits
I want to know how many new distributors will perform again on the next period
out of 399 recruits, we were able to convert 188 new distributors in period 1
now, im interested to know out of 188 new distributors how many distributors will perform again on Period 2
Hello @waterphil,
You are a new member and i can say that this post doesnt help at all to understand your issue.
Could you please provide us with some more information, visuals or even a sample dataset so as to understand your problem better?
Period No | Recruits | New Distributors | % | # Distributors from previous Period | |
1 | 100 | 399 | 188 | 47 % | |
2 | 89 | 531 | 303 | 57 % | |
3 | 96 | 493 | 209 | 42 % | |
4 | 90 | 367 | 232 | 63 % | |
5 | 89 | 369 | 163 | 44 % | |
6 | 85 | 324 | 177 | 55 % | |
7 | 75 | 512 | 200 | 39 % | |
8 | 88 | 292 | 112 | 38 % | |
9 | 92 | 296 | 144 | 49 % | |
10 | 93 | 306 | 149 | 49 % | |
11 | 89 | 199 | 118 | 59 % | |
12 | 103 | 67 | 19 | 28 % |
ex. out of 188 new distributors in period 1
i want to know how many distributors sell again in period 2
Hi,
Do you have any ID column for distributors, first of all?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |