Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional nested sumifs

Hi experts

 

I am having trouble adding the if statement in bold below:

 

BalanceVAT = if('powerbifinance'[KPI_Code]="816999",'powerbifinance'[Balance_at_Date_Actual]-sumx(FILTER('powerbifinance','powerbifinance'[Date]=EARLIER('powerbifinance'[Date])&&'powerbifinance'[KPI_Code]="940999"),if('powerbifinance'[Balance_at_Date_Actual]>0,'powerbifinance'[Balance_at_Date_Actual],0),'powerbifinance'[Balance_at_Date_Actual]))
 
I get the following error: Too many arguments were passed to the SUMX function. The maximum argument count for the function is 2. The custom column works without the bold if statement.

The purpose of the statement is to only subtract the filtered value in case it is larger than 0. What am I doing wrong?
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , I think one 'powerbifinance'[Balance_at_Date_Actual] is additional

     

    check

     

    BalanceVAT = if('powerbifinance'[KPI_Code]="816999",'powerbifinance'[Balance_at_Date_Actual]-sumx(FILTER('powerbifinance','powerbifinance'[Date]=EARLIER('powerbifinance'[Date]) && 'powerbifinance'[KPI_Code]="940999" ), if('powerbifinance'[Balance_at_Date_Actual]>0,'powerbifinance'[Balance_at_Date_Actual],0)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , I think one 'powerbifinance'[Balance_at_Date_Actual] is additional

     

    check

     

    BalanceVAT = if('powerbifinance'[KPI_Code]="816999",'powerbifinance'[Balance_at_Date_Actual]-sumx(FILTER('powerbifinance','powerbifinance'[Date]=EARLIER('powerbifinance'[Date]) && 'powerbifinance'[KPI_Code]="940999" ), if('powerbifinance'[Balance_at_Date_Actual]>0,'powerbifinance'[Balance_at_Date_Actual],0)))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      That did the trick! Thanks 🙂