Formula help

[unnijoy]

I have a table with EMPID,MONTH and SALARY. Every month most of the employeeid will remain same. Siome times new EMPID may added or some may be removed. When i try to calculate the total salary for like three month the formula is taking the same amount repetedly. Now i need a formula which will calculate the slarly in such a way that if the same employee id is repoeating it should take that employee id and salary only once.

MONTH	EMPID	Salary
5/1/2018	101	1000
5/1/2018	102	20000
5/1/2018	103	60000
5/1/2018	104	80000
5/1/2018	105	50000
6/1/2018	101	1000
6/1/2018	102	20000
6/1/2018	103	60000
6/1/2018	104	80000
6/1/2018	105	50000

In short if am selecting both May and june the overall salary should come 211000. 

[Anonymous · Accepted solution]

@unnijoy,

Create the following columns in your table.

Monthnumber = MONTH(Table[MONTH])
Rank = RANKX(FILTER(Table,Table[EMPID]=EARLIER(Table[EMPID])),Table[Monthnumber],,ASC)

Then create the following measures in your table.

multiselect = CALCULATE(sum(Table[Salary]),FILTER(Table,Table[Rank]=1))

result = IF(HASONEFILTER(Table[Monthnumber]),SUM(Table[Salary]),[multiselect])

Regards,
Lydia