Forum Discussion
Difference between two measures/rows
Hello,
I have following measure:
FC =
SUMX (
PLANTABLE,
SWITCH (
TRUE (),
PLANTABLE[Quarter] = 1, if( PLANTABLE[KNT] = "SbA" && PLANTABLE[Lay] = "FCI" , PLANTABLE[Value],blank()),
PLANTABLE[Quarter] = 2, if( PLANTABLE[KNT] = "SbA" && PLANTABLE[Lay] = "FCII" , PLANTABLE[Value],blank()),
PLANTABLE[Quarter] = 3, if( PLANTABLE[KNT] = "SbA" && PLANTABLE[Lay] = "FCIII" , PLANTABLE[Value],blank()),
PLANTABLE[Quarter] = 4, if( PLANTABLE[KNT] = "SbA" && PLANTABLE[Lay] = "FCIIII" , PLANTABLE[Value],blank()),
blank())
and
IST = if( PLANTABLE[KNT] = "SbA" && PLANTABLE[Lay] = "IST" , PLANTABLE[Value],blank())
Last formula is valid for all quarters. I created a third measure like DIFF = IST - FC. But the problem is that I have for example for quarter 1 two rows with 0 - FC and IST - 0. Not really what i wanted. Some ideas how to implement?
Something is wrong. What is Lay = "FC"? FC is the name of the measure. For Lay we have the entries Ist, FCI, FCII, FCIII, FIIII.
I think the best way is a PIVOT before. Makes it easier.
3 Replies
At the moment for quarter 2 it looks like following
What i want is the difference between IST - FC. But may i should pivot the table before when it is difficult via measure...
Essentially what you need to do is calculate the FC amount on the IST row and reverse.
You need to do this based on a unique identifier, which looks like it is KNT.Measure = VAR KNT = SELECTEDVALUE(Table[KNT]) VAR LAY = SELECTEDVALUE(Table[Lay]) Return IF( Lay = "FC" , CALCULATE( [IST] , ALL(Table) , Table[KNT] = KNT Table[Lay] = "Ist") - [FC] , [IST] - CALCULATE( [FC] , ALL(Table) , Table[KNT] = KNT Table[Lay] = "FC") )
Let me know if you need any more help .
/ JSomething is wrong. What is Lay = "FC"? FC is the name of the measure. For Lay we have the entries Ist, FCI, FCII, FCIII, FIIII.
I think the best way is a PIVOT before. Makes it easier.
