topic Re: How to sum given the filter context in DAX Commands and Tips

How to sum given the filter context

cld32650 — Fri, 16 Aug 2024 23:17:03 GMT

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

Re: How to sum given the filter context

Irwan — Fri, 16 Aug 2024 23:49:08 GMT

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.

Re: How to sum given the filter context

cld32650 — Sat, 17 Aug 2024 00:55:15 GMT

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?

Re: How to sum given the filter context

Irwan — Sat, 17 Aug 2024 01:01:48 GMT

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.

Re: How to sum given the filter context

Anonymous — Fri, 30 Aug 2024 03:59:02 GMT

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

Best Regards,

Leroy Lu