UPDATE ROW

[sohailstsindia]

I have Employee No, Component  and Pay, I have problem with ESB calulation. formula for ESB calulation is (basic *12/365)*21/365. Can any budy help me i am new in Power BI

(5000*12/365)*21/365 = 287.67

(7000*12/365)*21/365 = 402.74

EMP NO	COMPONENT	PAY
EM0001	BASIC	5000
EM0001	TRANSPORT	1000
EM0001	HRA	3000
EM0001	ESB
EM0002	BASIC	7000
EM0002	TRANSPORT	1000
EM0002	HRA	3000
EM0002	ESB

After calulation,

EMP NO	COMPONENT	PAY
EM0001	BASIC	5000
EM0001	TRANSPORT	1000
EM0001	HRA	3000
EM0001	ESB	287.67
EM0002	BASIC	7000
EM0002	TRANSPORT	1000
EM0002	HRA	3000
EM0002	ESB	402.74

[Zubair_Muhammad · Accepted solution]

@sohailstsindia

Ok I got it

5000*21/365 = 287.67

So you can adjust the formula above

Revised Pay =
IF (
    TableName[COMPONENT] = "ESB",
    VAR BasicSalary =
        CALCULATE (
            SUM ( TableName[PAY] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[EMP NO] ),
                TableName[COMPONENT] = "BASIC"
            )
        )
    RETURN
        BasicSalary * 21
            / 365,
    TableName[PAY]
)

So you get

[Zubair_Muhammad]

@sohailstsindia

(5000*12/365)*21/365 = 9.46 

How did you compute 287.67?

[Zubair_Muhammad]

@sohailstsindia

Anyways, you can add a new Calculated Column

Revised Pay =
IF (
    TableName[COMPONENT] = "ESB",
    VAR BasicSalary =
        CALCULATE (
            SUM ( TableName[PAY] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[EMP NO] ),
                TableName[COMPONENT] = "BASIC"
            )
        )
    RETURN
        ( BasicSalary * 12
            / 365 )
            * ( 21 / 365 ),
    TableName[PAY]
)

[Zubair_Muhammad]

@sohailstsindia

[Zubair_Muhammad · Accepted solution]

@sohailstsindia

Ok I got it

5000*21/365 = 287.67

So you can adjust the formula above

Revised Pay =
IF (
    TableName[COMPONENT] = "ESB",
    VAR BasicSalary =
        CALCULATE (
            SUM ( TableName[PAY] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[EMP NO] ),
                TableName[COMPONENT] = "BASIC"
            )
        )
    RETURN
        BasicSalary * 21
            / 365,
    TableName[PAY]
)

So you get

[sohailstsindia · Accepted solution]

Thank you very much Zubair for your reply, its work perfactly. Jazak Allah Khairan.

[sohailstsindia]

I have Employee No, Component  and Pay, I have problem with ESB calulation. formula for ESB calulation is (basic *12/365)*21/365. Can any budy help me i am new in Power BI

(5000*12/365)*21/365 = 287.67

(7000*12/365)*21/365 = 402.74

EMP NO	COMPONENT	PAY
EM0001	BASIC	5000
EM0001	TRANSPORT	1000
EM0001	HRA	3000
EM0001	ESB
EM0002	BASIC	7000
EM0002	TRANSPORT	1000
EM0002	HRA	3000
EM0002	ESB

After calulation,

EMP NO	COMPONENT	PAY
EM0001	BASIC	5000
EM0001	TRANSPORT	1000
EM0001	HRA	3000
EM0001	ESB	287.67
EM0002	BASIC	7000
EM0002	TRANSPORT	1000
EM0002	HRA	3000
EM0002	ESB	402.74