Forum Discussion

Shamatix's avatar
Shamatix
Post Partisan
7 years ago
Solved

SUM function only accepts column reference as an Argument

Hey fellow power bi users,

 

I am getting the following error: 

SUM function only accepts column reference as an Argument

 

My DAX is as follow:

Column = SUM(IF(AND(WEEKNUM(Data[dte_invduedate];2) >= WEEKNUM(TODAY();2); YEAR(Data[dte_invduedate]) <= YEAR(TODAY()));Data[TotalAmount];0))
 
What I want to do is retirm TotalAmount if the above is true and each time it aint true totalamount should be 0
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Shamatix

    it seems you write a column to put the formula which should be put in a measure.

    If you'd like a column more than a measure, you could write columns as below:

     

    total amount for all row

    Column =
IF (
    AND (
        WEEKNUM ( Data[dte_invduedate], 2 ) >= WEEKNUM ( TODAY (), 2 ),
        YEAR ( Data[dte_invduedate] ) <= YEAR ( TODAY () )
    ),
    SUM ( Data[TotalAmount] ),
    0
)

    total amount for each row

    Column =
IF (
    AND (
        WEEKNUM ( Data[dte_invduedate], 2 ) >= WEEKNUM ( TODAY (), 2 ),
        YEAR ( Data[dte_invduedate] ) <= YEAR ( TODAY () )
    ),
    CALCULATE ( SUM ( Data[TotalAmount] ) ),
    0
)

    Best Regards

    Maggie

6 Replies

  • riteshgaur's avatar
    riteshgaur
    Frequent Visitor
    7 years ago

    Hi,

     

    You should put your SUM inside a CALCULATEE function and apply required filters then.

     

    Regards

    Ritesh

    • Shamatix's avatar
      Shamatix
      Post Partisan
      7 years ago

      How so?

       

      Column = CALCULATE(SUM(IF(AND(WEEKNUM(Data[dte_invduedate];2) >= WEEKNUM(TODAY();2); YEAR(Data[dte_invduedate]) <= YEAR(TODAY()));Data[TotalAmount];0))) 
       
      Brings me errors
  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    Shamatix

     

    Try the following formula on a Measure and not Calculated column

     

    Measure =
CALCULATE (
    SUM ( Data[TotalAmount] );
    FILTER (
        ALL ( Data );
        WEEKNUM ( Data[dte_invduedate]; 2 ) >= WEEKNUM ( TODAY (); 2 )
            && YEAR ( Data[dte_invduedate] ) <= YEAR ( TODAY () )
    )
)
    • Shamatix's avatar
      Shamatix
      Post Partisan
      7 years ago
      themistoklis wrote:

      Shamatix

       

      Try the following formula on a Measure and not Calculated column

       

      Measure =
CALCULATE (
    SUM ( Data[TotalAmount] );
    FILTER (
        ALL ( Data );
        WEEKNUM ( Data[dte_invduedate]; 2 ) >= WEEKNUM ( TODAY (); 2 )
            && YEAR ( Data[dte_invduedate] ) <= YEAR ( TODAY () )
    )
)

      Hey Themis,


      Thanks for the try, but didnt work.

      As you can see it gives me the sum of ALL the rows in each row, I just want TotalAmount pr row where the statement at the top is valid, if it aint valid it should print 0,00 rather than the TotalAmount for the row.

      • themistoklis's avatar
        themistoklis
        Community Champion
        7 years ago

        Shamatix


        Can you please share with us the raw data or the workspace itself?

         

        Also if you want to send an image with the desired output that would be great.

         

        On the image this is the background table. What do you want toshow on PowerBi report though.

        Which fields and measures?

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Shamatix

    it seems you write a column to put the formula which should be put in a measure.

    If you'd like a column more than a measure, you could write columns as below:

     

    total amount for all row

    Column =
IF (
    AND (
        WEEKNUM ( Data[dte_invduedate], 2 ) >= WEEKNUM ( TODAY (), 2 ),
        YEAR ( Data[dte_invduedate] ) <= YEAR ( TODAY () )
    ),
    SUM ( Data[TotalAmount] ),
    0
)

    total amount for each row

    Column =
IF (
    AND (
        WEEKNUM ( Data[dte_invduedate], 2 ) >= WEEKNUM ( TODAY (), 2 ),
        YEAR ( Data[dte_invduedate] ) <= YEAR ( TODAY () )
    ),
    CALCULATE ( SUM ( Data[TotalAmount] ) ),
    0
)

    Best Regards

    Maggie