Calculate distinct customer-Id per day

[clarissa]

Hello

I have a table with following schema:

Date                           Customer ID

13.1.2020                    A

13.1.2020                    A

13.1.2020                    B

20.1.2020                    C

1.2.2020                      B

14.2.2020                    A

I need to count the distinct Customer-ID per day which serves as the number of orders, so the result would be

A= 2

B= 2

C=1

This column "number of orders" should sum up over a filtered time. I tried it with this formula, but the sum of the counted rows per month doesn't show the right total: 

Number of Orders =

Var NumberOfOrders = CALCULATE(DISTINCTCOUNT('Mart DimCustomer'[Customer_ID]); ALLSELECTED('Mart FactInvoice'[date]))

Return CALCULATE(NumberOfOrders)

Any recommendations how to solve this? Thank you!

[harshnathani]

Hi @clarissa ,

Create a measure

Distinct Count of Customer Day = COUNTROWS(SUMMARIZE('Table','Table'[Date]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[clarissa]

Hi Harsh

Thank you for your feedback. Unfortunately the number shows way too less orders when I calculate it this way... I only get 12 orders per month April although it should be much more.... Could it be that this formula only counts distinct customer_ID per filtered time frame? So if a customer orders 4x in April and I filter April, that I only get 1x the customer?

I would need every customer ID per day as 1 count and if that customer ID appears 2x or 3x it should count it only 1x per day..

Was it clear what I try to achieve?

Thanks again for your help and best regards, Clarissa

[mahoneypat]

When you tried the expression by @harshnathani did you visual have Customer ID in it?  In any case, if you modify that expression as follows, it should work whether it is or not.

Distinct Count of Customer Day = COUNTROWS(SUMMARIZE('Table','Table'[Date], 'Table'[customer_ID]))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[clarissa]

thank you Pat. 

Can you explain me, what this Summary function with the 2 groups (date, ID) actually does? 

and why does this formula not work?

CALCULATE(DISTINCTCOUNT('Tabe'[Customer_ID]))

Would it just count the distinct customer IDs in the time period I would filter in the table? so if the time period changes it would also change the count to the new time period and only count distinct IDs?

Thank you!

[stevedep]

Hi,

Try SUMX(disctinctcount ... 

You need an interator to sum subtotals.

Kind regards, Steve. 

[clarissa]

Hi Steve, 

thank you. I tried it with your proposal:

Count Orders = SUMX('Mart FactInvoice'; (DISTINCTCOUNT('Mart FactInvoice'[CustomerID]))) 

but it counts several duplicate customer IDs for the same day:

Best regards

Clarissa

[stevedep]

Perhaps something more explicit:

Sumx(values(table[date], distinctcount..

[clarissa]

Hi Steve

so you would write it like this?

Count Orders = SUMX(VALUES('Mart FactInvoice'[Date]);DISTINCTCOUNT('Mart FactInvoice'[customer-id]))

[stevedep]

Yes, if this doesn't work we need to know the data model.

[clarissa]

Hi Steve

Unfortunately it doesnt work.

The data model:

- 1 table factinvoice with revenues and invoice dates (*)

- 1 table with customer (1)

- join through customer_id: 1 to *

Does that help? Thanks for your support!

[lbendlin]

You'll want to have different calculations depending if you are in a "row" or in the total. Use HASONEVALUE() to differentiate.