Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
there is a table i want to put progress of progress against plan.
= start plan / start actual
thanks for any help!
thanks,
Hi,
Assuming Start Plan# and Start Actual# are measures, write this measure and format it as %.
Progress=[Start Plan#]/[Start Actual#]
Hope this helps.
got the error message.
the value start plan and actual are from different table. is that the reason i got the error?
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?
Hi,
Try this
Progress = DIVIDE ( SUM( 'table1'[Start Plan#] ) , IF(ISBLANK(SUM ( 'table2'[Actual Plan# )),10,SUM ( 'table2'[Actual Plan# ) ))
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.
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# ) )))
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,
> 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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |