Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to calculate the percentage of the difference between Estimated Price and Actual Cost.
My current calculation works fine as long as the estimated Price is >0:
This is the calculation i used for "% Difference from Est" column:
SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])
/
SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])
(the set analysis is there because I only wanted the calculation to work for items that were already purchased)
I know my calculation returns "-" because Estimated Cost is zero but I don't know how to change it so that it returns 100% when the Estimated Cost is zero but the Actual Cost is positive. Can you please help me?
Try sthing like this,
if(sum("Actual Cost")=0,1,
(SUM([Actual Cost])- SUM([Estimated Cost]))
/
SUM([Estimated Cost])
try:
alt(
SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])
/
SUM({$<[Actual Cost]={">0"}>}[Estimated Cost]),
1
)
---------------------------
Thanks Omar and Robin,
These options work great for cases where Actual Cost >0 and Estimated Cost = 0.
However they also show 100% for cases where both Actual Cost and Estimated Cost equal zero.
Before, such lines (where both values = 0) were suppressed because I unticked the "show zero values" but this change makes them reappear since I now have 100% against these lines.
Any other option?
if(
not isnull(SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])/SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])),
SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])/SUM({$<[Actual Cost]={">0"}>}[Estimated Cost]),
if(
(SUM({$<[Actual Cost]={">0"}>}[Actual Cost])=0) and ((SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])=0)),0,1
)
)
maybe
if(sum("Actual Cost")=0 and sum([Estimated Cost])=0,0,
if(sum("Actual Cost")=0 and sum([Estimated Cost])>0,1,
(SUM([Actual Cost])- SUM([Estimated Cost]))
/
SUM([Estimated Cost])