Powerpivot If statement - calculate price based on number size

[Evans2]

Hi all,

I'm trying to create a new calculated column TOTAL PRICE based on three other columns, PRICE, LBS and MT (metric ton).

the PRICE column has two currency formats which are USD (3 digits long) and USD/Metric ton (4 digits long).

I need to create a formula that will multiply PRICE * MT when PRICE is 4 digits long ELSE multiple PRICE * LBS.

I would think I would use a CALCULATE IF function to do this but I'm having some trouble with the syntax.

My attempts:

=CALCULATE(IF([PRICE]=####,[PRICE]*[MT],[PRICE]*[LBS]))


=CALCULATE(IF([PRICE]="####",[PRICE]*[MT],[PRICE]*[LBS]))
- This one comes back with an error "cannot find column PRICE" which is weird because it exists.

Any help is appreciated

Thanks

 

[Matt Allington]

It is best practice to always specify the table name followed by the column name in your formulas. This prevents confusion between columns and measures.

You don't need to calculate function for this calculation. I am not in front of my PC so I can check, but I believe there is a LEN function or length function that you could use to check that number of digits in your price column

 

[Evans2]

Does anyone know what function I could use for this? I don't think the LEN function is the right one.

I tried this:

=IF(LEN([PRICE],4),([PRICE]*[MT]),([PRICE]*[LBS]/100))

But I get an error : Too many arguments were passed to the LEN function. The maximum argument count for the function is 1.

 

[SimonNU]

=if( len( [price] ) = 4,

Although in saying that, I think you're approaching your problem from the wrong angle.