Forum Discussion
Summation during filtering
Hello all
I have a dataset similar to below.
| Year | Category | # Instances |
| 2021 | A | 15 |
| 2021 | B | 15 |
| 2021 | C | 15 |
| 2022 | A | 10 |
| 2022 | B | 20 |
| 2022 | C | 30 |
| 2023 | A | 45 |
| 2023 | B | 39 |
| 2023 | C | 24 |
I want to be able to filter for 2023 and see something similar to the output below
| Category | 2023 Instances | Total Aggregated Instances |
| A | 45 | 70 |
| B | 39 | 74 |
| C | 24 | 69 |
Thanks in advance!
3 Replies
Wilson_Memorable Member
Hello ALGH,
Try these two measures:
Total Aggregated Instances = SUM ( 'Table'[# Instances] ) 2023 Instances = CALCULATE ( [Total Aggregated Instances], 'Table'[Year] = 2023 )I end up with your expected result, using your data:
It also looks like you could benefit from some basic DAX training. 🙂 I'm a big fan of SQLBI. Check this out as a good starting point.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)- Wilson_
If you wanted dynamic measures that work with a year slicer on the page, the second measure above can look like this instead:
Selected Year Instances = CALCULATE ( [Total Aggregated Instances], 'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] ) )If you wanted to show the total regardless of your year filter, you would need another measure that looks like:
All Years Instances = CALCULATE ( [Total Aggregated Instances], REMOVEFILTERS ( 'Table'[Year] ) )
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)