Summation during filtering

[ALGH08]

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!

[Wilson_]

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?)

[ALGH08]

thanks - i think this works, but gives a specific situational result.  What happens if / when I decide to filter on 2022?  Do i need to create another measure?

My dataset is quite large and i wouldnt want to make a new measure for each possibility

[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?)