Aggregate a calculated column

[Tmk123]

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

[FreemanZ]

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:

[Tmk123]

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

[FreemanZ · Accepted solution]

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.