calculate column value

[sohailstsindia]

TABLE1
EMP NO	COMPONENT	PAY
EM0001	BASIC	5000
EM0001	HRA	300
EM0001	TRNSPORT	500
EM0001	AIR TICKET
EM0002	BASIC	7000
EM0002	HRA	300
EM0002	TRNSPORT	500
EM0002	AIR TICKET

	TABLE2
EMP NO	SECTOR ID	NO OF TICKET
EM0001	SEC-001	2
EM0002	SEC-002	1
EM0003	SEC-001	3

TABLE 3
SECTOR ID	TICKET FAIR
SEC-001	2500
SEC-002	6000
SEC-003	1500

I need to calculate air ticket amount based on no off ticket, like EM001 =PAY =2*2500 =5000   

EM002 = 1*6000

TABLE1
EMP NO	COMPONENT	PAY
EM0001	BASIC	5000
EM0001	HRA	300
EM0001	TRNSPORT	500
EM0001	AIR TICKET	5000
EM0002	BASIC	7000
EM0002	HRA	300
EM0002	TRNSPORT	500
EM0002	AIR TICKET	6000

[Anonymous · Accepted solution]

Since it looks like you're trying to add values into the existing Table1, I recommend that you do this work in Query Editor as opposed to DAX.

I propose the following method:

1. In QE, merge Table2 with Table3, join on Sector ID.
2. Merge this new table with Table1, join on EMP NO.  Now you have access to the [Ticket Fare] and [Number of Ticket] columns
3. Create a custom column with the following logic:
   1. if [PAY] = null then [Ticket Fare] * [Number of Tickets] else [PAY]
4. Delete the original [PAY] column, and the columns that were brought in from the merge operations (they're no longer needed and will bloat your data model).
5. Rename your custom column to [PAY], and you should be all set.

[Anonymous · Accepted solution]

Since it looks like you're trying to add values into the existing Table1, I recommend that you do this work in Query Editor as opposed to DAX.

I propose the following method:

1. In QE, merge Table2 with Table3, join on Sector ID.
2. Merge this new table with Table1, join on EMP NO.  Now you have access to the [Ticket Fare] and [Number of Ticket] columns
3. Create a custom column with the following logic:
   1. if [PAY] = null then [Ticket Fare] * [Number of Tickets] else [PAY]
4. Delete the original [PAY] column, and the columns that were brought in from the merge operations (they're no longer needed and will bloat your data model).
5. Rename your custom column to [PAY], and you should be all set.

[Zubair_Muhammad]

@sohailstsindia

I believe the 3 tables would have a relationship like this.

[Zubair_Muhammad · Accepted solution]

@sohailstsindia

If you have above relationships, you can add a calculated column in Table1

Revised Pay =
IF (
    Table1[COMPONENT] = "Air Ticket",
    RELATED ( Table2[NO OF TICKET] ) * RELATED ( Table3[TICKET FAIR] ),
    Table1[PAY]
)

[sohailstsindia]

Thanks Zubair, Its worked.