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 filte...
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

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