Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Table Measure

Hi there is a table i want to put progress of progress against plan. = start plan / start actual   thanks for any help! thanks,
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, i tried and the result look like below.

they put 0% if there is no progress but plan.

but it made others calculated the other around.

 

thanks,

d_gosbell's avatar
d_gosbell
Super User
7 years ago

> but it made others calculated the other around.

 

What do you mean by this? Ashish's answer looks like it's calculating exactly how you requested ( plan / actual )

 

So the two lines you've highlighted  52/ 51 = 102%  and  10 / 2  = 500% appear to be work exactly as you requested. I did query this in my earlier response as I would have expected that the calculation would have made more sense as (actual / plan).

 

You might also want to consider making use of variables in this expression as it should make it easier to read. And if the actual / plan are around the wrong way you can just swap this in the DIVIDE function.

 

eg

 

Progress = 
VAR _actualSum = SUM ( 'table2'[Actual Plan# )
VAR _planSum = SUM( 'table1'[Start Plan#] )
RETURN IF(ISBLANK(_actualSum)
  ,0
  , DIVIDE ( _planSum , IF(ISBLANK(_actualSum),10, _actualSum ))
)

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    I'm not sure if that helps as you've swapped the logic for cases 2 & 3 from what you stated earlier in this thread.

     

    But I think the easier way to handle these 3 cases is to just use 2 nested IF's

     

    eg. 

     

    Progress = 
    VAR _actualSum = SUM ( 'table2'[Actual Plan# )
    VAR _planSum = SUM( 'table1'[Start Plan#] )
    RETURN IF(ISBLANK(_actualSum)
      ,0
      IF(ISBLANK(_planSum)
        , _actualSum 
        , DIVIDE ( _planSum , _actualSum    )
      )
    )

     

    Although the logic above is using the original definitions, not the cases you just posted, but hopefully the above is simpler to understand and you can change it around if needed

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    I wanted to have 3 cases (actual/ plan) in same coloum

     

                   actual      plan       actual/ plan            progress %

    case 1 :     2             2     => actual / Plan        => 100%

    case 2 :    blank      3      =>  blank/ 3               =>  0%

    case 3 :     3            blank  =>3 / blank              => 300%

     

    is it clear??

    thanks,