# IF statment (DAX) in powerpivot



## ehardway123 (May 14, 2014)

Hello, 
I am having a had time having this function work properly. I am creating a proper P and L sheet using powerpivot. Meaning I want both my expenses and revenue as positive value on the sheet. Right now the revenue show up as negative and expenses as positive. So I need to make the Revenue Positive and also keep the expenses positive also. This website showed me how by using HASONEVALUE using an DAX IF statement but its not working properly. I get the error message, The syntax ',' is incorrect.  What I have is Account type which is Revenue and expenses, and then I have account group which is subcatgories under those and account names which is services/products under the account group and also division amount, which is the  dollar amount of each revenue/expense. the following is the formula I used:

=([DIVISION AMT])*-1)*IF(HASONEVALUE([ACCOUNT TYPE])), IF(VALUES([ACCOUNT TYPE])= "EXPENSES" ,-1, 1), 1)


----------



## KMacauley (May 14, 2014)

Hi ehardway,

A quick question, why not use the ABS command? The ABS command converts negative values to positive.

I added a calculation of =ABS([Division Amt]), and then a column calculation of Sum of ABS Value:=SUM([ABS Value])

Kim.


----------



## ehardway123 (May 14, 2014)

Kim,

That would not work. In a proper Profit/Loss sheet, the revenue would be debit and be positive and expenses are credit and be positive too, but sometimes a expense it is a debit which would show as negative on the sheet since because it is not credit like the rest of the expenses. That cannot be shown as an absolute value. Thanks for your help anyways.


----------



## KMacauley (May 14, 2014)

Hi ehardway,

Apologies, I should have realized. I simplified the function to the one below.


```
=([Division Amt]*-1) * IF([Account Type]="EXPENSES", -1, 1)
```


I'm not quite sure what the aim of using HASONEVALUE([ACCOUNT TYPE]))?


Kim


----------



## KMacauley (May 14, 2014)

KMacauley said:


> Hi ehardway,
> 
> Apologies, I should have realized. I simplified the function to the one below.
> 
> ...



Hi ehardway,

I also did some quick reading of HASONEVALUE vs. ISFILTERED vs. HASONEFILTER « PowerPivotPro and then made some slight adjustments to your function. I did the following:


Removed the extra ) from ([DIVISION AMT])*-1)
Change HASONEVALUE to HASONEFILTER
Changed VALUES([ACCOUNT TYPE]) to [ACCOUNT TYPE]


```
=([DIVISION AMT]*-1)* IF(HASONEFILTER([ACCOUNT TYPE]), IF([Account Type]="EXPENSES", -1, 1), 1)
```

Kim.


----------



## Tianbas (May 15, 2014)

Why not using a "normal" if statement ? =if(Table[account type]="revenue",-Table[Amount],Table[Amount])


----------



## ehardway123 (May 15, 2014)

Kim,

Both the codes did not work. On the  one where you simplified, it still had the expenses be negative. The second one with HASONEFILTER made the revenue become negative. I want all to be shown positive,unless it is a credit in the revenue and debit in expenses. Thanks for the help of it tho. Do you have an idea what else to do?


----------



## ehardway123 (May 15, 2014)

Tianbas,

I tried this and it gave me an error message. It said syntax Table is incorrect.


----------



## scottsen (May 15, 2014)

I like Tianbas's answer, you just need to replace his generic "Table" with the actual name of YOUR Table (which we dont know...)


----------



## ehardway123 (May 15, 2014)

I am not sure what my Table name is. How do I know what it is?


----------



## ehardway123 (May 14, 2014)

Hello, 
I am having a had time having this function work properly. I am creating a proper P and L sheet using powerpivot. Meaning I want both my expenses and revenue as positive value on the sheet. Right now the revenue show up as negative and expenses as positive. So I need to make the Revenue Positive and also keep the expenses positive also. This website showed me how by using HASONEVALUE using an DAX IF statement but its not working properly. I get the error message, The syntax ',' is incorrect.  What I have is Account type which is Revenue and expenses, and then I have account group which is subcatgories under those and account names which is services/products under the account group and also division amount, which is the  dollar amount of each revenue/expense. the following is the formula I used:

=([DIVISION AMT])*-1)*IF(HASONEVALUE([ACCOUNT TYPE])), IF(VALUES([ACCOUNT TYPE])= "EXPENSES" ,-1, 1), 1)


----------



## scottsen (May 15, 2014)

On the powerpivot window, EVERY table is on a tab, with it's own name.  Looks basically like a Sheet name in excel.


----------



## ehardway123 (May 15, 2014)

Yes! It worked. Thank you so much!


----------

