How to sum given the filter context

[cld32650]

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:

Date	CategoryA	CategoryB	Value
1/31/2020	1	M	1.2
1/31/2020	2	M	5.3
1/31/2020	1	N	2.7
1/31/2020	2	N	8.2
2/28/2020	1	M	0.8
2/28/2020	2	M	4.3
2/28/2020	1	N	0.6
2/28/2020	2	N	6.1
3/31/2020	1	M	6.2
3/31/2020	2	M	5.7
3/31/2020	1	N	3.6
3/31/2020	2	N	1.8

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

Date	CategoryA	CategoryB	Calendar Year Sum of Value
2020	1	M	8.2
2020	2	M	15.4
2020	1	N	7.0
2020	2	N	16.1
2021	1	M	10.5
2021	2	M	20.7
2021	1	N	20.4
2021	2	N	14.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

[Irwan]

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]

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?

[Anonymous]

Hi, @cld32650 
Can you tell me if your problem is solved? If yes, please accept Irwan'reply  as solution.

Best Regards,

Leroy Lu

[Irwan]

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.