Forum Discussion
Count rows for each ID and aggregate
I have this sample table:
| Id | Date | Product | Running Total |
| 1 | 2017-01-01 | 0 | 3 |
| 2 | 2018-01-01 | 0 | 3 |
| 3 | 2019-01-01 | 0 | 3 |
| 4 | 2017-01-01 | 1 | 6 |
| 5 | 2020-01-01 | 1 | 6 |
| 6 | 2021-01-01 | 1 | 6 |
| 7 | 2021-01-01 | 2 | 7 |
| 8 | 2022-01-01 | 3 | 8 |
| 9 | 2022-01-01 | 4 | 9 |
| 10 | 2017-01-01 | 5 | 11 |
| 11 | 2022-01-01 | 5 | 11 |
"Running Total" is a MEASURE (NOT a column), and I need to change this measure such that it works when the date column is filtered.
Current code for "Running Total", which generates the above output:
Issue with the code: It does not work when the "date" column is filtered using a slicer.
I need this output when the date filter is set to 2018-01-01-2023-01-01 for example:
| Id | Date | Product | Running Total |
| 2 | 2018-01-01 | 0 | 2 |
| 3 | 2019-01-01 | 0 | 2 |
| 5 | 2020-01-01 | 1 | 4 |
| 6 | 2021-01-01 | 1 | 4 |
| 7 | 2021-01-01 | 2 | 5 |
| 8 | 2022-01-01 | 3 | 6 |
| 9 | 2022-01-01 | 4 | 7 |
| 11 | 2022-01-01 | 5 | 8 |
As you can see, 2017 dates are removed, therefore the "Running Total" measure is adjusted accordingly.
How to achieve this?
ALLSELECTED did the trick.
3 Replies
msuser48 You will need to use ALLEXCEPT instead of ALL. Also, this might be of interest: https://community.powerbi.com/t5/Quick-Measures-Gallery/Better-Running-Total/m-p/2755666#M885
Greg_Deckler
can you elaborate how the allexcept should look like? I tried AllExcept(table,product), which still does not work.ALLSELECTED did the trick.
