topic Aggregate a calculated column in DAX Commands and Tips

Aggregate a calculated column

Tmk123 — Fri, 10 Nov 2023 02:54:26 GMT

Hi,

I am having trouble aggregating a calculated column correctly.

My calculated column is below. My goal is for it to add all the Amounts for each Catalog # for the year 2022. It is actually working, however, when I select a slicer that is on the report for Division and/or Catalog #, the column still aggregates the total for all Divisions. This is what I have but I know it is incorrect. I am hoping someone can help. Thank you so much!

_2022 Invoice Amt =

CALCULATE(

SUM('_Sum Sales By Div'[Invoice Amt]),

'Calendar'[Year] = 2022,

ALLEXCEPT('_Sum Sales By Div', '_Sum Sales By Div'[Catalog #])

)

In my table, I want the calculated column to show the 2022 total for Catalog #

If no slicers were selected the value in the calculated column for 20384 would be 18
If a Division slicer was selected for "ABH" 20384 would only show 6.

Sample data

Year	Division	Catalog #	Invoice Qty	Invoice Amt
2022	ABH	20384	1	6
2022	ABH	30461	4	8
2022	UE	20384	2	12
2023	ABH	20384	6	36
2023	ABH	30461	8	16
2023	UE	20384	3	18

Re: Aggregate a calculated column

FreemanZ — Fri, 10 Nov 2023 03:24:14 GMT

Hi Tmk123 ，

Not sure if i fully get you.

try to plot a slicer with division column and table visual with catelog column and a measure like:

Amt 2022 = CALCULATE( SUM(data[Invoice Amt]), data[Year]=2022 )

it worked like:

Re: Aggregate a calculated column

Tmk123 — Fri, 10 Nov 2023 12:35:35 GMT

Thanks, that is what I am trying to do, however, received a circular dependency error.

A circular dependency was detected: _Sum Sales By Div[_Amt % Change], _Sum Sales By Div[_2022 Invoice Amt], _Sum Sales By Div[_Amt % Change].

This is the updated column, based on your suggestion:

_2022 Invoice Amt =

CALCULATE(

SUM('_Sum Sales By Div'[Invoice Amt]),

'_Sum Sales By Div'[Year] = 2022

)

I had another calculated column calculating the % Change. I cannot use measures in my table because I have a slicer controlling the % Change. Is there a way to rewrite this to fix the circular dependency?

_Amt % Change = DIVIDE([_2023 Annualized Amt] - [_2022 Invoice Amt], [_2022 Invoice Amt])

Re: Aggregate a calculated column

FreemanZ — Fri, 10 Nov 2023 14:48:50 GMT

hi Tmk123 ,

the suggested code is for measure.

Calculated columns are not supposed to respond to the visuals, like the slicer, that you mentioned in the original post.