Forum Discussion

Joe89's avatar
Joe89
Regular Visitor
2 years ago
Solved

New Column based on Condition and Calculation

Hello,

 

I am trying to create a line grap showing a "Value" per "Phase" and respective limits. the limits are being calculated based on the previous phase value. The value resulting out of the phases is depending on the phase outcomes so might change up/down/stay the same.

 

Phase Limit Rules:

Phase 1 => Value of P1 +/- 100% Limit

Phase 2 => Value P1 +/-50% Limit

Phase 3 => Value P2 +/-30% Limit

Phase 4 => Value P3 +/-10% Limit

 

Example Table of desired result. Phase and Value is what I have, Lower Limit and Upper Limit is what I am trying to create. I guess the solution for the two columns is in principle the same but with respective sign change in the calculation.

ProjectPhaseValueLower LimitUpper Limit
A110020
A220515
A3101426
A410911
B15010
B252,57,5
B353,56,5
B454,55,5
C120040
C2101030
C320713
C401822

 

Would be grateful for any help!

 

Many thanks,

 

Jens

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PhilipTreacy ,thanks for the quick reply, I'll add more.

    Hi Joe89 ,

    The Table data is shown below:

    Use the following DAX expression to create columns

    Low Limit = 
VAR _phase = [Phase]
VAR _Project = [Project]
VAR _table = ALL('Table')
VAR _result = 
SWITCH(TRUE(),
_phase = 1 , [Value] * 0,
_phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.5,
_phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.7,
_phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.9
)
RETURN _result
    Upper Limit = 
VAR _phase = [Phase]
VAR _Project = [Project]
VAR _table = ALL('Table')
VAR _result = 
SWITCH(TRUE(),
_phase = 1 , [Value] * 2,
_phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.5,
_phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.3,
_phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.1
)
RETURN _result

    Final output

     

    Best Regards,
    Wenbin Zhou

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi Joe89 

     

    I'm not folloing the logic to create the table of values.

     

    What is the value of Limit?  For example Phase 2 => Value P1 +/-50% Limit and in this case, what is P1?

     

    Phil

    • Joe89's avatar
      Joe89
      Regular Visitor
      2 years ago

      Quick answer to your question. Value P1 = Value from Phase 1

  • Joe89's avatar
    Joe89
    Regular Visitor
    2 years ago

    Hello Phil,

     

    and thanks for taking the time to read!

     

    Indeed the limit values are based on the previous phase value (except for P1 where it keeps its value)

    Another example:

    ProjectA has the Phase and Values as shown below. Where Phase is the project progress and value for example its investment needed.

    Phase 1: Value 10, as there is no previous phase it will take its own value 10 (however this phase 1 boundary is optional / not the core issue)

    -> Lower Limit in PDP1 = -100% so the value is 0 for Lower Limit

    -> Upper Limit in PDP1 = +100% so the value is 20 for Upper Limit

    Phase 2: Value 15 of this phase. But for the limit it takes the previous phase value of 10

    -> Lower Limit in PDP2 = -50% so the value is 5 for Lower Limit

    -> Upper Limit in PDP2 = +50% so the value is 15 for Upper Limit

    Phase 3: Value is 10 (again, this one is not following any logic it is a given quantity), but it takes the previous phase value of 15 for the limits.

    -> Lower Limit in PDP3 = -30% so the value is 10.5 for Lower Limit

    -> Upper Limit in PDP3 = +30% so the value is 19.5 for Upper Limit

     

    Hope this helps to explain it.

    The problem I am facing revolves mainly around taking that value for a project and its phase, changing it via a calulcation. And then putting it into the next phase as the upper / lower limit. 

    I tried something of the likes of looking up the project name, an if clause to the phase with a calculation and then return this value to a variable which is then set as the value for the next phase limit. However this did not work and returned 0 for the next phase value all the time.

     

    For some context. I am trying to create a graph based on the recorded data of cost and phase for projects to see if their increase or decrease in budget exceeded the threshold set by the phase. The further the project progresses in phases, the more mature it is and the smaller these limits for changes in budget become.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi PhilipTreacy ,thanks for the quick reply, I'll add more.

      Hi Joe89 ,

      The Table data is shown below:

      Use the following DAX expression to create columns

      Low Limit = 
VAR _phase = [Phase]
VAR _Project = [Project]
VAR _table = ALL('Table')
VAR _result = 
SWITCH(TRUE(),
_phase = 1 , [Value] * 0,
_phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.5,
_phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.7,
_phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.9
)
RETURN _result
      Upper Limit = 
VAR _phase = [Phase]
VAR _Project = [Project]
VAR _table = ALL('Table')
VAR _result = 
SWITCH(TRUE(),
_phase = 1 , [Value] * 2,
_phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.5,
_phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.3,
_phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.1
)
RETURN _result

      Final output

       

      Best Regards,
      Wenbin Zhou