Forum Discussion

JohannB's avatar
JohannB
Helper I
8 years ago

Multip[le sum functions

I am currently trying to convert a excel worksheet to Pwower BI but cannot find a proper solution.

The formula used in Excel is '=SUM(((L15*65%)*70%)*10%)+(L15*65%)*70%

The one I am usesing in Power Bi is 

Calc = if(LEFT(Query1[StudentRelationsOfficer].3)="PRO".SUM(((Query1[MonthlyInstallmentAmount]*65%)*70%)*10%)+(Query1[MonthlyInstallmentAmount]*65%)*70%,'SUM(((Query1[MonthlyInstallmentAmount]*90%)+(Query1[less_1_Balance]*10%)

The data i am using is the following 

 

 

Will appreciate any help

Thank you 

Need Help

4 Replies

  • ConorHG's avatar
    ConorHG
    Helper I
    8 years ago

    Hi JohannB, 

     

    I can't be 100% sure but try this calc: 

     

    Calc = if(LEFT(Query1[StudentRelationsOfficer],3)="PRO",
        SUMX(<TABLE>,(((Query1[MonthlyInstallmentAmount]*.65)*.70)*.10)+
        ((Query1[MonthlyInstallmentAmount]*.65)*.70)),
      SUMX(<TABLE>,(Query1[MonthlyInstallmentAmount]*.90)+(Query1[less_1_Balance]*.10)))

    where <TABLE> is your Query1 table.

     

    Thanks, 
    Conor.

    • JohannB's avatar
      JohannB
      Helper I
      8 years ago

      Thank you Conor, The data is a table that is imported from SQL. The formula keeps on giving me the following error

      • ConorHG's avatar
        ConorHG
        Helper I
        8 years ago

        Hi JohannB,

         

        You nearly have it. You need to have commas ',' instead of full stops '.' and sumx takes two parameters, the table and the query. 

         

        You need to add the table to to start of each sumx calc.

        sumx(<TABLE>,<QUERY>)

        Regards,

        Conor.