7 Replies Latest reply on Mar 27, 2018 11:03 AM by Deepak Rai

# Splitting 1 measure into 2 based on positive and negative numbers?

Hello,

Please can anyone help me out, I've really tried just about everything (I've grouped, i've binned, etc) and believe this should be a calculated field but cannot figure out the formula.

I have a measure named liability. It contains both positive and negative numbers. I need to split these numbers apart and have 1 measure for anything that is 0 and above and another measure that is for everything below a 0.

Can anyone assist. i don't want to reformat my source data and have two sheets separating positive / neg numbers as a work around,

Thanks

Deepak

• ###### 2. Re: Splitting 1 measure into 2 based on positive and negative numbers?

Yes of course, see attached data set.

• ###### 3. Re: Splitting 1 measure into 2 based on positive and negative numbers?

Hello Natalie,

Have you tried the following calculations:

POSITIVE_LIABILITY

if [Liability] >= 0 then [Liability] else null end

NEGATIVE_LIABILITY

if if [Liability] < 0 then [Liability] else null end

If you hide nulls for both, you'll be able to show the individual measures. May not work depending on your use case though-- elaborate on how you want the final view to look!

Hope this helps,

Lisa

1 of 1 people found this helpful
• ###### 4. Re: Splitting 1 measure into 2 based on positive and negative numbers?

See if this Helps and if it, Then Pl mark it Helpful and CORRECT.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 5. Re: Splitting 1 measure into 2 based on positive and negative numbers?

@lisa li -  This is perfect. I cannot thank you enough. I do not see any nulls so it works out!

@deepak rai - This is what I need as well, can you tell me what you did, it's a very interesting take on this issue I have!

• ###### 6. Re: Splitting 1 measure into 2 based on positive and negative numbers?

I created 2 Calculations and worked rowwise so that you don't miss any value. I used FIXED LOD and using ZN function to convert any NULL to 0.

1 of 1 people found this helpful
• ###### 7. Re: Splitting 1 measure into 2 based on positive and negative numbers?

Thanks. Pl close the thread marking whichever answer you feel is CORRECT.