Forum Discussion

Chanleakna123's avatar
Chanleakna123
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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. 

 

Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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]
)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Chanleakna123 

    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.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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]
)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Chanleakna123 

    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.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM