Calculating amount of customers

[Anonymous]

Hello everyone,

In our system, new customers and customers who are leaving our company are measured each month. So, the amount of customers per month is reliable. However, I want to measure this for the whole year. But when I use DistinctCount([Customer_ID]), that number isn't reliable, since it is way to high. DistinctCount counts all the customers in that year, even if they left the company. So, I need the average of the twelve months to have a reliable value. Is there a way to do this?

[Arjunarao]

Hi Johan94,

Please check my solution.

[v-shex-msft]

Hi @Anonymous ,

You can write formula to calculate distinct count of customer grouped by 'year', 'month', then use summarize function to summary these records and do average calculation on year level to get correspond result.

If you are confused on coding formula ,please share some sample data for test.

Regards,

Xiaoxin Sheng

[Anonymous]

Hey @v-shex-msft ,

We work with ID-numbers for our customers. Since customers show up many times in a column (almost every day, since we've contact with them on a daily basis), a measure have been created: amount of customers = Distinctcount(Customers[Customer-ID]). 

When I create a matrix table, I get the following numbers:

Those numbers are our unique customers, but this number is too high (see image below), since not all of those are customers anymore. So, that is why I want the average of the twelve months.

The other value I've used, is our date column, which is Calender[Date].

I hope I've provided you with enough information. Otherwise let me know.

[v-shex-msft]

Hi @Anonymous ,

Please share some sample data with similar data structure so that we can test to coding formula on it. You can build same fake data and stored to excel file, then upload to onedrive or google drive and share link here.

Notice: do mask on sensitive data.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

I've created a sample data set of five months. The link is  https://drive.google.com/file/d/1glmiEFuszOCNmUI832xysnpTS-3_mVJg/view?usp=sharing

The column 'Number of customers' is a DAX-formula: DISTINCTCOUNT([Customer_ID]).

The number of customers per month reliable. However, the total of those five months isn't. The sample set is small, but when you've thousands of customers, it's a big deal. For example, we could have 3000 customers in January, and 4000 in February, and the total of that could be 4100. Because it counts the unique customers of those two months together, which isn't reliable (it is reliable per month, because we've contracts with our customers for a month). 

I hope this helps!

[v-shex-msft]

Hi @Anonymous ,

You can try to use following measure formula if it works.(I add a summary function to do summarize to get distinct results each month level and use average function to calculate on total level)

Measure =
AVERAGEX (
    SUMMARIZE (
        'Customer',
        [Year],
        [Month],
        "DC", DISTINCTCOUNT ( 'Customer'[Customer_ID] )
    ),
    [DC]
)

Regards,
Xiaoxin Sheng

[Anonymous]

Thank you very much! It works!

Could you explain why you've quotation marks and parenthesis for DC?

[v-shex-msft]

HI @Anonymous ,

You can take a look at following blog to know more about how to deal with measure calculated on total level:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng