IF statment (DAX) in powerpivot

[ehardway123]

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]

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]

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]

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]

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

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:

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

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

Kim.

 

[Tianbas]

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

 

[ehardway123]

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]

Tianbas,

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

 

[scottsen]

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]

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