cancel
Showing results for
Did you mean:
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.

2 ACCEPTED SOLUTIONS
Super User II

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]
)``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Community Champion

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

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.

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

2 REPLIES 2
Community Champion

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

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.

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

Super User II

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]
)``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements