Forum Discussion

Tmk123's avatar
Tmk123
Icon for Helper II rankHelper II
2 years ago
Solved

Aggregate a calculated column

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
YearDivisionCatalog #Invoice QtyInvoice Amt
2022ABH2038416
2022ABH3046148
2022UE20384212
2023ABH20384636
2023ABH30461816
2023UE20384318
"DAX help"
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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. 

3 Replies

    • Tmk123's avatar
      Tmk123
      Icon for Helper II rankHelper II
      2 years ago

      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's avatar
        FreemanZ
        Icon for Super User rankSuper User
        2 years ago

        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.