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

how to count distinct customer > 2

1.JPG

HI , 

i wanna count distinct of customers who purchased>2 , but above calculation doesn't work ,it show 7 which is count all Customers  . it is not right. the right number is only 2 Customers who Re-Purchased. 

PS :

Purchased Customer >0 

Repurchased Customer >2

 

how to acheive this ? 

 

2 ACCEPTED SOLUTIONS
v-xicai
Community Support
Community Support

Hi @Chanleakna123 ,

 

You may create measures like DAX below.

 

Count purchase= = CALCULATE(SUM('Fuze and Energy'[Purchased]),FILTER(ALLSELECTED('Fuze and Energy'), 'Fuze and Energy'[Customer Name - Store[Customer]] =MAX('Fuze and Energy'[Customer Name - Store[Customer]])))
 

Repurchased count= CALCULATE(DISTINCTCOUNT('Fuze and Energy'[Customer Name - Store[Customer]]),FILTER(ALLSELECTED('Fuze and Energy'), [Count purchase]>2))

 

Best Regards,

Amy

 

Community Support Team _ Amy

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

View solution in original post

@v-xicai , hi , now i have a condition , if the customers purchased at the same date for 2 times , we count only Purchased , 

but the calculation is turning to Re-Purchased as well which is wrong , while we need to count only Purchase Customer, But take out Re-Purchased 

1.JPG

View solution in original post

14 REPLIES 14
v-xicai
Community Support
Community Support

Hi @Chanleakna123 ,

 

You may create measures like DAX below.

 

Count purchase= = CALCULATE(SUM('Fuze and Energy'[Purchased]),FILTER(ALLSELECTED('Fuze and Energy'), 'Fuze and Energy'[Customer Name - Store[Customer]] =MAX('Fuze and Energy'[Customer Name - Store[Customer]])))
 

Repurchased count= CALCULATE(DISTINCTCOUNT('Fuze and Energy'[Customer Name - Store[Customer]]),FILTER(ALLSELECTED('Fuze and Energy'), [Count purchase]>2))

 

Best Regards,

Amy

 

Community Support Team _ Amy

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

In case the solution does not work out. Refer to the similar solution on

https://community.powerbi.com/t5/Desktop/Filter-by-the-sum-of-a-column-where-value-of-another-column...

 

 

@v-xicai , hi , now i have a condition , if the customers purchased at the same date for 2 times , we count only Purchased , 

but the calculation is turning to Re-Purchased as well which is wrong , while we need to count only Purchase Customer, But take out Re-Purchased 

1.JPG

Tahreem24
Super User
Super User

@Chanleakna123 ,

 

Try below measure:

M = CALCULATE(COUNT('Table'[Purchased]),'Table'[Purchased]>2)
 
Capture.JPG

Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

hi , i don't know what happen , 

but below is my purchased customers , is this related together ?

 

 

4.JPG

@Chanleakna123 ,

 

Did you try my solution? I have also posted screen shot of your expected answer.

 

Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

hi @Tahreem24  here 😞

 

5.JPG

 

hi , let's do it this way , 

looks at the metrix table below , if the customers keep purchasing >= 2 times based on selected period , they consider as Re-Purchased Outlets , The Outlet count is Distinct Count . 

as below cased , it count 2 outlets as Re-Purchased Outlets 

7.JPG

amitchandak
Super User
Super User

Try something like this

No of purchase = 
CALCULATE(DISTINCTCOUNT('Table'[purchase]))		  
		  
No of clients = COUNTX(filter(values('Table'[Client]),[No of purchase]>=2), [No of purchase])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

hi @amitchandak @JohanT @az38 , 

 

All doesn't work out , maybe it is impacted from Filter ? 2.JPG

Hm. No the filters affect the measure to show only the results from what you have filtered.

 

Can you post the DAX-code from your measure?

 

//J

@JohanT , All DAX all of you have provided , are turning to blanks , 

3.JPG

JohanT
Frequent Visitor

Hi @Chanleakna123,

 

I would use the COUTNROWS() function together with FILTER(). FILTER creates a filtered table with the critera to only show rows >= 2 and COUNTRORWS simply counts them.

 

In your case something like: Measure = COUTNROWS(FILTER(Customers;Purchased >=2))

 

image.png

az38
Community Champion
Community Champion

hi @Chanleakna123 

try a measure

Measure = 
CALCULATE (DISTINCTCOUNT('Fuze and Energy'),SUM('Fuze and Energy'[Purchased])>2)

do not hesitate to give a kudo to useful posts and mark solutions as solution

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.