Forum Discussion

snaraya's avatar
snaraya
Icon for Helper II rankHelper II
2 years ago
Solved

Need help to rectify the formula

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

  • snaraya's avatar
    snaraya
    2 years ago

    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 

3 Replies

  • snaraya's avatar
    snaraya
    Icon for Helper II rankHelper II
    2 years ago

    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 

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    2 years ago

    Hi , change your filter expression..

    VAR NextAmount =
    CALCULATE(
    MAX(Table1[Planned]),
    FILTER(

    Allexcept(
    Table1,
    Table1[ID]

    )
    Table1[Index] = (Table1[Index]) + 1
    )
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, snaraya 

    You can refer to Rupak_bi  reply, if it does not work, you can provide pbix files without sensitive data for testing(By Google Drive).

     

    Best Regards,
    Yang
    Community Support Team