Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
colourfullife
Post Partisan
Post Partisan

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
Ashish_Mathur
Super User
Super User

Hi,

 

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

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

got the error message.

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

errorq.PNG

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

Hi, thanks!

 

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

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

290%.PNG

Hi,

 

Try this

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

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,

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.