Forum Discussion
Solved
Keep Filter Selection within ALLEXCEPT
Hi,
I have Data in this format:
| Date | Name | Sales |
| 01.01.2022 | A | 10 € |
| 02.01.2022 | A | 10 € |
| 02.01.2022 | B | 100 € |
| 03.01.2022 | A | 10 € |
| 03.01.2022 | B | 100 € |
| 04.01.2022 | A | 10 € |
| 04.01.2022 | B | 100 € |
| 04.01.2022 | C | 1000 € |
Now I want to build the running sales total per Name and the user should be able to select more than one names.
My current DAX formula is:
Sales running total =
CALCULATE(
[Sales],
FILTER(
ALL(Table), [Date] <= MAX([Date])
)
)
This is working when all names are selected but not when the user is for example only selecting names A and B as the ALL filter is removing the name filter context completely.
I also tried this formula:
CALCULATE(
[Sales],
FILTER(
ALLEXCEPT(Table, Table[Names]), [Date] <= MAX([Date])
)
)
But then the totals for each names are calculated separately and the outcome is not a total over all names as wanted.
CALCULATE(
[Sales],
FILTER(
ALL(Table), [Date] <= MAX([Date])
)
)
This is working when all names are selected but not when the user is for example only selecting names A and B as the ALL filter is removing the name filter context completely.
I also tried this formula:
CALCULATE(
[Sales],
FILTER(
ALLEXCEPT(Table, Table[Names]), [Date] <= MAX([Date])
)
)
But then the totals for each names are calculated separately and the outcome is not a total over all names as wanted.
I somehow need to remove the name filter to be able to calculate the totals but in the same time keep the user selection.
How do I do that?
Don't use ALLEXCEPT, use ALLSELECTED
2 Replies
Don't use ALLEXCEPT, use ALLSELECTED
Hi,
Please check the below picture and the attached pbix file.
One of ways to achieve this is to use column in the measure, not the whole table.
Sales running total: = CALCULATE ( [Sales:], FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )
