Forum Discussion
Solved
Need help with Formula
Hello,
I need help with formula because I can't figure out why that simple formula doesn't works.
Example:
I have this table.
I used this formula:
COLUMN3 = IF('Planas vs Faktas'[Plan]-'Planas vs Faktas'[Fact]<=0;0;'Planas vs Faktas'[Plan]-'Planas vs Faktas'[Fact])
So, I want to subtract PLAN - FACT, by every month, and if FACT was bigger than PLAN, it have to be 0 in new column.
So, like you see in 2019-10-31 Fact was bigger than Plan. That's mean PLAN was achieved.
If PLAN was bigger than FACT, in new column its just have to be PLAN-FACT. But like you see by this formula Im just getting PLAN's numbers.
Hello EdgarasR1
You can create a measure like so to get your result.
Measure = VAR _Fact = SUM ( 'Planas vs Faktas'[Fact] ) VAR _Plan = SUM ( 'Planas vs Faktas'[Plan] ) RETURN IF ( _Fact > _Plan, 0, _Plan - _Fact )You should create measures for each of the amount though then use those measures.
Fact Amount = SUM ( 'Planas vs Faktas'[Fact] )Plan Amount = SUM ( 'Planas vs Faktas'[Plan] )Plan-Fact = [Plan Amount] - [Fact Amount]Then the final measure would be just this:
Measure = IF ( [Fact Amount] > [Plan Amount], 0, [Plan-Fact] )
1 Reply
Hello EdgarasR1
You can create a measure like so to get your result.
Measure = VAR _Fact = SUM ( 'Planas vs Faktas'[Fact] ) VAR _Plan = SUM ( 'Planas vs Faktas'[Plan] ) RETURN IF ( _Fact > _Plan, 0, _Plan - _Fact )You should create measures for each of the amount though then use those measures.
Fact Amount = SUM ( 'Planas vs Faktas'[Fact] )Plan Amount = SUM ( 'Planas vs Faktas'[Plan] )Plan-Fact = [Plan Amount] - [Fact Amount]Then the final measure would be just this:
Measure = IF ( [Fact Amount] > [Plan Amount], 0, [Plan-Fact] )