Forum Discussion

cld32650's avatar
cld32650
Frequent Visitor
2 years ago

How to sum given the filter context

I am trying to calculate the sum for the calendar year given the current filter context. I have data formatted as follows. On the visualization page, I am using a slicer to define CategoryA and am plotting other points in the table (not shown). Here is a preview of example data:

DateCategoryACategoryBValue
1/31/20201M1.2
1/31/20202M5.3
1/31/20201N2.7
1/31/20202N8.2
2/28/20201M0.8
2/28/20202M4.3
2/28/20201N0.6
2/28/20202N6.1
3/31/20201M6.2
3/31/20202M5.7
3/31/20201N3.6
3/31/20202N1.8

 

I need to calculate the sum of 'Value' for each calendar year, split by Categories. The anticipated result would be as follows: 

DateCategoryACategoryBCalendar Year Sum of Value
20201M8.2
20202M15.4
20201N7.0
20202N16.1
20211M10.5
20212M20.7
20211N20.4
20212N14.9

I have tried both:

 

CALCULATE(sum(Table[Value]), Table[Category] = SELECTEDVALUE('CategoryTable'[Category]))

CALCULATE(sumx(Table,[Value]), Table[Category] = SELECTEDVALUE('CategoryTable'[Category]))

 

What I can't figure out is how to aggregate by year. Note, I will be plotting the measure as the color index for an x,y scatter plot, so I cannot just plot it against the date axis and aggregate to year.

 

Any help would be greatly appreciated. Thanks

4 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    hello cld32650 

     

    please check if this accomodate your need.

    there are someways to do this but, let me write it in at least two ways.

     

    1. Using SUMMARIZE

    create new table with following DAX.

    Summarize =
    SUMMARIZE(
        'Table',
        'Table'[CategoryA],
        'Table'[CategoryB],
        "Year",
        SELECTCOLUMNS('Table',"Year",YEAR('Table'[Date])),
        "SumValue",
        SUM('Table'[Value]))

     

    2. Using table visual

    create calculate column for Year since you want to filter those data with Year.

    Year = YEAR('Table'[Date])

     next, plot it in table visual. Dont forget to only sum your 'Value' column

     

    Hope this will help.

    Thank you.

    • cld32650's avatar
      cld32650
      Frequent Visitor
      2 years ago

      I need this to be filterable by the filter context defined in my slicer. I thought tables were not able to be dynamically calculated based on slicer selection, correct?

      • Irwan's avatar
        Irwan
        Icon for Super User rankSuper User
        2 years ago

        hello cld32650 

         

        yes, you are right.

         

        if you use the SUMMARIZE, you probably will not be able to dynamically calculated since it will be in column.

         

        you need measure for dynamically calculated. the table visual should be work with slicer though.

        Here is the example, the value is changed based on slicer selection.

         

        Hope this will help.

        Thank you.