running totals count

[hpatel247]

Hi,

I'm trying to show a cumulative total count of users per month but not sure how to do this. So far i have managed to get close to it however in my data, i can have multiple users in the same month but it counts each row. e.g in April i have 8 rows but 6 unique users, in May i have 4 users all unique you the total i would expect to show in May would be 10 (6 + 4) and so on each month

I'm using the below measure:

RunningTotal = var rowdata = FTE[Month] return CALCULATE(SUM(FTE[MTD]),FILTER(FTE,FTE[Month]<=rowdata && YEAR(FTE[Month])=YEAR(rowdata)))

Can someone help me with this as i have tried different methods from posts that are out there but they all are based on total sales or costs etc but i need one similar but as a cumulative count and not double counting if that makes sense.

regards

Hetal

[Greg_Deckler · Accepted solution]

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.

[p0nk · Accepted solution]

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!

[p0nk · Accepted solution]

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!

[hpatel247]

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

[p0nk]

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?

[hpatel247]

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

[p0nk · Accepted solution]

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!

[hpatel247]

Thank you for your response. It has worked but will play around to fill in the blanks

[Anonymous]

HI,

were you able to fill in the Blanks?

[Greg_Deckler · Accepted solution]

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.