Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ...
Need Help
Wilson_'s avatar
Wilson_
Icon for Memorable Member rankMemorable Member
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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_'s avatar
      Wilson_
      Icon for Memorable Member rankMemorable Member
      3 years ago

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