How to get a correct total figure from distinct counts per customer

[ScottMarwood]

Hi All,

I need to produce a Measure which will count the number of products bought per customer from the beginning of the year to a given date. I can do this no problem but the measure also gives the total as the distinct count overall whereas I would like it to give a total by adding up each line (if that makes sense). I need this to be able to have a base figure of how many products are used by each customer overall.

I am using the following at the moment:

Products Ordered part 1 =

var _daterange = DATESBETWEEN(Dates[FullDate], DATE(2023,01,01),DATE(2023,08,31))

var _count = DISTINCTCOUNT(Orders[Article Number])

RETURN

CALCULATE(_count,_daterange)

Which produces:

I would like the total to be 54 for obvious reasons.

Any help would be much appreciated

[tamerj1 · Accepted solution]

Hi @ScottMarwood 

please try

Products Ordered part 1 =
SUMX (
VALUES ( orders[Clinet ID] ),
CALCULATE (
DISTINCTCOUNT ( Orders[Article Number] ),
DATESBETWEEN ( Dates[FullDate], DATE ( 2023, 01, 01 ), DATE ( 2023, 08, 31 ) )
)
)

[ScottMarwood]

Hi tamerj1, Thanks for the advice, certainly an improvement although one of the customers is now showing as blank, any idea why this may be?: 

[tamerj1]

@ScottMarwood 

Must be out of the date range specified in DATESBETWEEN

[ScottMarwood]

You are absolutely right! Thank you so much, I'm still not sure I understand the theory on how it works but it does!