Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
i am calculating a formula where
CurrentAmount / (CurrentAmount + NextAmount) * 1000000
example: 0.1/(0.1+59.9)*1000000 =1666.66
the test column formula is :
test =
VAR CurrentID = Table1[ID]
VAR CurrentAmount = Table1[Planned]
VAR NextAmount =
CALCULATE(
MAX(Table1[Planned]),
FILTER(
Table1,
Table1[ID] = CurrentID &&
Table1[Index] = EARLIER(Table1[Index]) + 1
)
)
RETURN
IF(
ISBLANK(NextAmount),
BLANK(),
CurrentAmount / (CurrentAmount + NextAmount) * 1000000
)
000165 i should get 1666.66 but i am getting 1714
000162 i am getting correct
please somebody check what am i doing wrong
Thanks in advance
Adding table in the comment
Solved! Go to Solution.
Issue got resolved, formula i used is correct . Only issue was because the planned column was not rounded off at query editor level causing output to be wrong . Thankyou
Issue got resolved, formula i used is correct . Only issue was because the planned column was not rounded off at query editor level causing output to be wrong . Thankyou
Hi , change your filter expression..
VAR NextAmount =
CALCULATE(
MAX(Table1[Planned]),
FILTER(
Allexcept(
Table1,
Table1[ID]
)
Table1[Index] = (Table1[Index]) + 1
)
)