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
Chanleakna123
Post Prodigy
Post Prodigy

Customer Count Distinct value based on slicer

HI ALL , 

our purpose is to find out how many customer purchased 1 time =Customer PUrchase and customer purchased more than 2 times Customer Repurchased ( based on the period selected ,We don't care which brand or flavor , but if they purchased we count all the Numer of Customer in Distinct Count. 

 

i have table below :

PS : Value = Purchased

i wanna find out : 
****Customer Purchased : based on the Date selected , If they have purchased >1 time = Customer Purchased ( We don't care which brand or flavor they purchased ) if they have value >0 we count them purchased ,and  Customer Purchased need to Count by distinct Customer 

Based on ex below : we have 3 customer Purchased  are 2THNOO,23 coffee,dalin grill  beacause all have value 

 

****Customer RePurchased : based on the Date selected , if they have purchased >2 Times of Date ( for ex : purchased on 15 Mar & 11 Jan & so on... ) = Customer Repurchased (We don't care which brand or flavor they purchased ) and Customer Repurchased need to Count by distinct 

Based on ex below : we have 1 customer RePurchased is 23 Coffee  

 

Everything will be calculated based on the slicer , of Date , Brand , Flavor and so on , 

Please note that All customer Count only Distinct 

 

1.JPG

1 ACCEPTED SOLUTION

Hi @Chanleakna123 ,

 

Please modify Measure2.

Measure 2 = SUMX(ALLEXCEPT('Table','Table'[Customer],'Table'[products]),[Measure])

Result would be shown as below.

2.PNG3.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @Chanleakna123 ,

 

Please refer to the Measures below.

Measure = IF(SELECTEDVALUE('Table'[Date])>=MIN('CALENDAR'[Date])&&SELECTEDVALUE('Table'[Date])<=MAX('CALENDAR'[Date])&&SELECTEDVALUE('Table'[Value])>0,1,0)

Customer Purchased = CALCULATE(DISTINCTCOUNT('Table'[Customer]),FILTER('Table',[Measure]>0))

Measure 2 = SUMX(ALLEXCEPT('Table','Table'[Customer]),[Measure])

Customer RePurchased = CALCULATE(DISTINCTCOUNT('Table'[Customer]),FILTER('Table',[Measure 2]>1))

Result would be shown as below.

1.PNG
Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

hi @v-jayw-msft  , 

i have done over this , Purchased is correct.

But Re-Purchased is not , because below Customer i highlight bought the different products at the same date on 18 Jan , which is sum to 5.58 , While these numers are sum up 2 times together , in term of Re-Purchased , We Don't count this customer into Re-Purchased. he just bought 2 times at the same date , so we don't count it. 

 

 

1.JPG

Hi @Chanleakna123 ,

 

Please modify Measure2.

Measure 2 = SUMX(ALLEXCEPT('Table','Table'[Customer],'Table'[products]),[Measure])

Result would be shown as below.

2.PNG3.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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.