Is it possible to have formulas in hierarchies?

[xxjq88]

Hi All,

First of all, I just wanted to say I am new to the bi community so I'm sorry if this sounds like an easy question.

I have created a profit & loss by using parent child hierarchies through dax, path function etc.. 

Currently my revenue and expenses which are positive and negative values which gives me the operating profit (top level). I was wondering is there a way I can change my expenses to a positive value and keep the formular (Revenue - Expenses).

I should mention my revenue and expenses are in the same column. 

Any help is much appreciated!

JQ. 

[Phil_Seamark]

HI @xxjq88

You could add a calculated column to your table and use a forumla like 

new column = 
 ABS(<existing column>

This will convert ALL negative values to positive. 

Otherwise an IF statement could be used for more specific logic

[xxjq88]

Hi Phil,

Yes you are correct, the ABS function does change all my negative values to positive.

But then, my net operation result will be incorrect because it will then be revenue + expenses.

[Phil_Seamark]

So do you need to make the other type negative?

Can you please post a small sample of what your data might look like now, and how you would like it to look 🙂

[xxjq88]

Hi there,

Hierarchy

As you can see above, that is my hierarchy that I have created. Right now it is pulling in the sum of revenue and negative expense to come up with operating result.

I want to be able to have both revenue and expense values as positive and come up with the correct operating result. 

Let me know if you need anything else. 

Edit:

Hi, maybe what i've said previous is a bit confusing. Lets start again.

I have created a parent - child hierarchy as you can see above. I have pulled in the values at the lowest levels to create revenue and expense. Currently my Operating Result before income tax (highest level) is the SUM of revenue and expense where expense is a negative value. for example: Operating Result = 1000 + (-500)

What I would like to do is to be able to have revenue and expense as both a positive number. E.g. Operating result = 1000 - 500.

I know i'll have to add a calculated measure somewhere, but just not sure how i can go about doing it.

Basically, I think I want to know if it is possible to create calculations within hierarchies.