Cumulative count of distinct values

[Anonymous]

Greetings,

I am sure this question must have been answered in this forum before but I will appreciate if someone can still help me.

I have a monthly table for which I want to calculate the count of sites visited by different people over time.

John=calculate(distinctcount(table[Location], Filter(table,table[Visited by]="John")) 

A measure like this can return the number of unique sites visited by each person. However I can't figure out how to go about finding the cumulative count of sites over months or years and do YTD or YoY calculations.

Any idea?

Many thanks.

[Ashish_Mathur · Accepted solution]

Select a Year and Month in the slicer.  To your Table visual, drag Visited By and then drag the measures that i suggested.  If you still do not get your desired result, then share the download link of your PBI file.

[Anonymous]

Hi @Jihwan_Kim Hi @Ashish_Mathur 

Thank you both for trying to assist me but I'm afarid its not working.

@Ashish_Mathur I have created a date table (Calender) but in your measure there is no reference to the column "Visted By" so all it does is just give the number of distinct sites which is 10. I would like a measure which gives the YTD or cumulative count of distinct sites visted by each person. 

I've a measure John=calculate(distinctcount(table[Location], Filter(table,table[Visited by]="John")).

I tried to calculate YTD for John with 

YTDJohn = TOTALYTD([John],Calender[Date]) but strangely it returns 5 whereas it should be 4+2+3 =9

Any other suggestion? 🙂

[Ashish_Mathur · Accepted solution]

Select a Year and Month in the slicer.  To your Table visual, drag Visited By and then drag the measures that i suggested.  If you still do not get your desired result, then share the download link of your PBI file.

[Anonymous]

@Jihwan_Kim @Ashish_Mathur 

Apologies I forgot to clarify that the source file adds monthly data.  The number of sites is currently 10 and number of people is 3 but both may change over time. 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First create a slicer table for Month and year,then create a measure as below:

Measure = 
var _monthno=CALCULATE(MAX('Month table'[Month No.]),FILTER('Month table','Month table'[Month]=SELECTEDVALUE('Month table'[Month])))
VAR _date=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),MONTH('Table'[Date])<=_monthno&&'Table'[Year]=SELECTEDVALUE('Month table'[Year])))
Return 
IF(MAX('Table'[Date])<=_date,CALCULATE(DISTINCTCOUNT('Table'[Location]),FILTER(ALL('Table'),'Table'[Visited by]=MAX('Table'[Visited by])&&YEAR('Table'[Date])=SELECTEDVALUE('Month table'[Year])&&MONTH('Table'[Date])<=MONTH(MAX('Table'[Date])))),BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft Thanks for your time and providing a solution. It as well as that of @Ashish_Mathur does answer my question in as far as the way I could formulate it, namely the cumulative count of distint sites YTD per person.

However what I failed to explain is that I needed the YTD count of distinct sites per person relative to each month. Basically the sum of monthly outcomes. Lets say Mike visited 3 distinct sites in jan, 4 in feb,  3 march so the YTD measure should give 10.

Probabaly the problem I wanted  help with is less problematic than its formulation/description. 🙂

[Ashish_Mathur]

Hi,

Ideally you should build a Calendar table and via calculated column formulas, extract Year, Month name and Month number.  Sort the Month name column by Month number.  Create a relationship between the Date column of your Data Table to the Date column of your Calendar Table.  Create slicers for Year and Month Name and select any one Year and Month name.  To your Table visual, drag Visited By.  Write these measures:

Sites visited = distinctcount(data[location])

YTD sites visited = calculate([sites visited],datesytd(calendar[date],"31/3"))

Hope this helps.

[Anonymous]

Hi @Jihwan_Kim 

Thanks for your quick reply.  However your proposed measure just returns the total count of all the sites. Basically just the line count.(75). 

Sorry if I couldnt make myself clear.

What I need is a way to find the cumulative count of sites for each person. For example , John visted distinct sites 4 in January ,2 in February and 3 in march so cumulative count for him should be 9. But my distinctcount measure give 5.

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I could only see from 1st jan to 13th Jan. But your other sample shows Jan Feb Mar. 

I could not test my fixed DAX measure properly to check by myself.

So, please kindly check the below-fixed measure whether I properly followed your instruction.

I checked it with the sample that shows 13th Jan, and I think it works.

I am not sure how it will look like when it applied to your data model.

Cumulate Count of  DistinctSitesVisit by person =

CALCULATE (

DISTINCTCOUNT('Table'[Location]),

FILTER (

ALLEXCEPT ( 'Table','Table'[Visited By] ),

'Table'[Date] <= MAX ( 'Table'[Date] )

)

)

Thank you.

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.

[Jihwan_Kim]

Hi, @Anonymous 

Please try the below MEASURE to calculate a cumulative count of each site visit by each person as the date passes.

Cumulate Count of Sites by date =
CALCULATE (
SUMX ( 'Table', 1 ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Visited By] ),
'Table'[Date] <= MAX ( 'Table'[Date] )
)
)

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.