cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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,

table measure.PNG

11 REPLIES 11
Super User
Super User

Re: Table Measure

Hi,

 

Assuming Start Plan# and Start Actual# are measures, write this measure and format it as %.

 

Progress=[Start Plan#]/[Start Actual#]

 

Hope this helps.

Highlighted

Re: Table Measure

got the error message.

the value start plan and actual are from different table. is that the reason i got the error?

errorq.PNG

d_gosbell Member
Member

Re: Table Measure

This error probably means that "Start Plan#" and "Start Actual#" are just columns and Power BI is helpfully creating implied measures to aggregate these. So if you right click on one of the two tables and choose the "New Measure..." option then enter something like the following it should work:

 

Progress = DIVIDE ( SUM( 'table1'[Start Plan#] ) , SUM ( 'table2'[Actual Plan# ) )

 

Note: you'll need to replace 'table1' and ''table2' with the actual names of your 2 tables

 

And I would have expected that you would want to do actual / plan so that you could say that you've reached x% of the plan...

Re: Table Measure

Hi, thanks!

 

it worked!! but in this case (yellow highlight)

i want to see 290% there..? is that possible?

290%.PNG

Super User
Super User

Re: Table Measure

Hi,

 

Try this

 

Progress = DIVIDE ( SUM( 'table1'[Start Plan#] ) , IF(ISBLANK(SUM ( 'table2'[Actual Plan# )),10,SUM ( 'table2'[Actual Plan# ) ))

Re: Table Measure

Hi,

Hi,

it worked! thanks very much!

one last question, can i put 0% or something in the other way?

because i am thinking to colour them base on the perscontage in next step so feel like need to put some figure in it to remark.

 

something is in.PNG

Super User
Super User

Re: Table Measure

Hi,

 

Try this

 

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

Re: Table Measure

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,

The other around.PNG

d_gosbell Member
Member

Re: Table Measure

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 ))
)