cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chanleakna123
Post Prodigy
Post Prodigy

Distinct Count and then Total Customers based on Month Selected

hi all , 

i have below data in daily basis recorded by each transaction in 1st table.

and 2nd table is the result i want. 

 

Currently i use the formula of distinct count which means when i filter 3 months together we lost some customers due to the same customer code is not counted. but what we want is , Total Distinct Customers by Month and then when the user filter 3 months together , it will count total distinct customers by each month together.

 

i have master table of calendar and Master Data as relationship. 

 

Capture.PNG

2 ACCEPTED SOLUTIONS
PaulDBrown
Super User II
Super User II

@Chanleakna123 

1) Create the distinct count measure:

Customer Distinct count =
DISTINCTCOUNT ( Table[Customer Code] )

2) Use this final measure:

Final count of Customers =
SUMX (
    SUMMARIZE (
        'Calendar Table',
        'Calendar Table'[Month],
        "@bymonth", [Customer Distinct count]
    ),
    [@bymonth]
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @Chanleakna123 

Please check  the below picture and the sample pbix file's link down below.

 

Picture2.png

 

Distinct Count Customer =
DISTINCTCOUNT(Sales[Customer Code])
 
Distinct Count Customer Total Fix =
SUMX(VALUES(Dates[Month Name]), [Distinct Count Customer])
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Jihwan_Kim
Community Champion
Community Champion

Hi, @Chanleakna123 

Please check  the below picture and the sample pbix file's link down below.

 

Picture2.png

 

Distinct Count Customer =
DISTINCTCOUNT(Sales[Customer Code])
 
Distinct Count Customer Total Fix =
SUMX(VALUES(Dates[Month Name]), [Distinct Count Customer])
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

PaulDBrown
Super User II
Super User II

@Chanleakna123 

1) Create the distinct count measure:

Customer Distinct count =
DISTINCTCOUNT ( Table[Customer Code] )

2) Use this final measure:

Final count of Customers =
SUMX (
    SUMMARIZE (
        'Calendar Table',
        'Calendar Table'[Month],
        "@bymonth", [Customer Distinct count]
    ),
    [@bymonth]
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors