4 Replies Latest reply on Oct 4, 2018 6:53 AM by meenu choudhary

# True|False Calculation: When measures move in the same direction, then true, else false

Hi everyone,

I am pretty stumped on a calculation. I have looked through the forum and google without much luck. Unfortunately, I can't post the workbook due to financial information.

I need a T|F that does as follows:

IF [measure 1] increases relative to the previous period AND [measure 2] increases relative to the same previous period THEN "TRUE"

ELSEIF [measure 1] decreases relative to the previous period AND [measure 2] decreases relative to the same previous period THEN "TRUE"

ELSE "FALSE"

Essentially, I just need a true false statement that says true if two measures rise or fall together. In the example above, I would want to filter out true so I can see the exceptions in the view, where they do not move in the same direction. They don't have to increase the same amount, they just have to be directionally true to each other.

Thank you for your help.

• ###### 1. Re: True|False Calculation: When measures move in the same direction, then true, else false

Hi Michael,

Does the attached cover this condition, comparing the change in sum(Sales) with the change in avg(Discount) in Superstore data ?

I created the Difference quick table calculations and then dragged them to the data pane as new calculated fields.

I also left the 2 'both' conditions as separate results for now.

IF [Sales Change] > 0 AND [Discount Change] > 0

THEN 1

ELSEIF  [Sales Change] < 0 AND [Discount Change] < 0

THEN 2

ELSE 0

END

Thanks,

Jonathan

• ###### 2. Re: True|False Calculation: When measures move in the same direction, then true, else false

Hi Michael,

Its always nice to attache sample data.

I have taken below example from superstore:

You can try below approach

1. previous profit = LOOKUP(sum([Profit]),-1)

2. previous sales = LOOKUP(sum([Sales]),-1)

3. Flag =

if (SUM([Sales])-[previous sales]) > 0 AND (sum([Profit])-[previous profit])> 0 THEN 'TRUE'

ELSEif (SUM([Sales])-[previous sales]) < 0 AND (SUM([Profit])-[previous profit])< 0 THEN 'FALSE'

ELSE 'FALSE' END 1 of 1 people found this helpful
• ###### 3. Re: True|False Calculation: When measures move in the same direction, then true, else false

This methodology worked perfectly. Thank you.

• ###### 4. Re: True|False Calculation: When measures move in the same direction, then true, else false

Happy to know that it helped you .