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!
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
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!
Thank you for your response. It has worked but will play around to fill in the blanks
HI,
were you able to fill in the Blanks?