Forum Discussion
running totals count
Try the Running Total quick measure. You could potentially replace the SUM with COUNT for the aggregator. If you can post sample data could provide a more complete answer.
I belive you are looking for a measure like this:
Running total of unique users per month = CALCULATE(DISTINCTCOUNT(TableName[UserColumn]), FILTER(ALL(TableName), TableName[Month] <= TableName[Month])))
More information on the data you are using would be helpful.
Hope this helps!
Distinct users = CALCULATE(COUNT(Table_Name[User ID]), FILTER(ALL(Table_Name), Table_Name[Month] <= MAX(Table_Name[Month])), VALUES(Table_Name[Category]))
This measure will display a table like this:
To 'fill-in the gaps' as in your example I think you would have to use a calculated column which is more difficult (and I am unsure of). This will give you the same results but leave the cells blank if no change has occured. You may be able to find a way to fuill in these gaps.
Hope this helps!
I belive you are looking for a measure like this:
Running total of unique users per month = CALCULATE(DISTINCTCOUNT(TableName[UserColumn]), FILTER(ALL(TableName), TableName[Month] <= TableName[Month])))
More information on the data you are using would be helpful.
Hope this helps!
Thanks for your response. I have replaced SUM with DISTINCTCOUNT and it works. However i now need to know how to count users more than once if they appear in 2 different categories.
I have tried using COUNT and COUNTA but still doesn't count the user in each category. Is there a way round this
regards
Hetal
I don't quite understand what you mean by different categorys? Are you able to post a sample of your data and an example of the result you want to achieve?
A Sample of what i want to achieve
This is my Data:
User ID Category Month 1 Triage Apr-17 1 Youth Conditional Caution Apr-17 2 Triage May-17 2 Youth Caution Jul-17 3 Youth Conditional Caution May-17 3 Youth Caution Apr-17 4 Youth Caution May-17 5 Youth Conditional Caution Jun-17 6 Youth Conditional Caution Jun-17 7 Youth Caution Jul-17 So individual month totals per category should show as:
Apr-17 May-17 Jun-17 Jul-17 Triage 1 1 0 0 Youth Caution 1 1 0 2 Youth Conditional Caution 1 1 2 0 Therefore i want my running totals to show as:
Running Total Apr-17 May-17 Jun-17 Jul-17 Triage 1 2 2 2 Youth Caution 1 2 2 4 Youth Conditional Caution 1 2 4 4 Hope this makes sense
regards
Hetal
