cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dave-ExpSC
Helper II
Helper II

Nested IF Statement too many arguments

Can someone help me with this formula.

 

In my DATA table I can make this IF statement work:

=IF(OR([eSource]="Electricity",[eSource]="Natural Gas"),[Quantity]*0.0036,IF([eSource]="Diesel",[Quantity]*0.0364,""))

 

However if I take it one step further it fails:

=IF(OR([eSource]="Electricity",[eSource]="Natural Gas"),[Quantity]*0.0036,IF([eSource]="Diesel",[Quantity]*0.0364,IF([eSource]="LPG"),[Quantity]*.0000049,""))

Formula check highlights "LPG"

 

How can I use the IF statement to allow more than 3 criteria

 

Alternatively I tried this formula within Powerpivot that only produces and Error result

=SUMX(ENERGYtbl,IF(ENERGYtbl[eSource]="Natural Gas",ENERGYtbl[Quantity]*.0036,IF(ENERGYtbl[eSource]="Electricity",ENERGYtbl[Quantity]*.0036,IF(ENERGYtbl[eSource]="LPG",ENERGYtbl[Quantity]*.0000049,IF(ENERGYtbl[eSource]="Diesel",ENERGYtbl[Quantity]*.0364,"")))))

 

I need to have a formula that allows me to keep adding to the formula as new energy sources get added to the project.

Help is much appreciated!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

To answer your second question:

 

Alternatively I tried this formula within Powerpivot that only produces and Error result

=SUMX(ENERGYtbl,IF(ENERGYtbl[eSource]="Natural Gas",ENERGYtbl[Quantity]*.0036,IF(ENERGYtbl[eSource]="Electricity",ENERGYtbl[Quantity]*.0036,IF(ENERGYtbl[eSource]="LPG",ENERGYtbl[Quantity]*.0000049,IF(ENERGYtbl[eSource]="Diesel",ENERGYtbl[Quantity]*.0364,"")))))

 

You'd be better off storing the multipliers (0.0036, 0.0036, 0.0000049, ...) in your model as an additional column: ENERGYtbl[multiplier].

 

You then be able to create a calcaulted column 

[Something] = ENERGYtbl[Quantity] * ENERGYtbl[multiplier]

which can easily be summed

 

[Total] = SUM(ENERGYtbl[Something])

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

To answer your second question:

 

Alternatively I tried this formula within Powerpivot that only produces and Error result

=SUMX(ENERGYtbl,IF(ENERGYtbl[eSource]="Natural Gas",ENERGYtbl[Quantity]*.0036,IF(ENERGYtbl[eSource]="Electricity",ENERGYtbl[Quantity]*.0036,IF(ENERGYtbl[eSource]="LPG",ENERGYtbl[Quantity]*.0000049,IF(ENERGYtbl[eSource]="Diesel",ENERGYtbl[Quantity]*.0364,"")))))

 

You'd be better off storing the multipliers (0.0036, 0.0036, 0.0000049, ...) in your model as an additional column: ENERGYtbl[multiplier].

 

You then be able to create a calcaulted column 

[Something] = ENERGYtbl[Quantity] * ENERGYtbl[multiplier]

which can easily be summed

 

[Total] = SUM(ENERGYtbl[Something])

Thank you very much that's a good idea.

Anonymous
Not applicable

Looks like you have an extra closing brace

 

=IF(OR([eSource]="Electricity",[eSource]="Natural Gas"),[Quantity]*0.0036,IF([eSource]="Diesel",[Quantity]*0.0364,IF([eSource]="LPG"),[Quantity]*.0000049,""))

 

 

Edit: and possibly missing a closing one at the end.

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!