Need help to sum depending on mutliple conditons.

[rajbi2020]

Hi All, I need some help .. 

I have 3 tables where one is BILLING ,users list and team ,currentteam. with Employe and current dept .I wanted to calculate the sum for each YTD for EMPLOYEE ,by team . 

1 Senario 

 if employee never change the team get sum (amt)  irrrespective of the dept .

2. 

 If employee changed the teams from 11 to 22  and  we select 22 which is currect team . then only display his current dept sum(amt) ie BBC 20 and If we select 11 then  display sum(other amount) ie leave his current team dept amout . Ie 

 sum(amount) not inclusive of DEPT BBC

I am trying to create a measure for this . using calculate and filters .. but i am not sucessfull

Actual = CALCULATE(SUM(Billed[AMOUNT]),FILTER(Employeeytd ,Employeeytd[DEPT]=FIRSTNONBLANK(CurrentUser[DEPT],TRUE())))

1  Billing table

EMPLOYEE	period(year&month)	Dept	Amount
1	201912	ABC	10
1	202001	ABC	12
1	202002	BBC	14
2	201912	ABC	10
2	202001	ABC	12
2	202002	DBF	14
2	202003	BBC	20
3	201912	CDE	19
2	202001	BBC	10

 2 Employee ytd TABLE

EMPLOYEE	period(year&month)	team
1	201912	11
1	202001	11
1	202002	11
1	202003	11
2	201912	11
2	202001	22
2	202002	22
3	201912	44

3 .Current user table latest month.

employee	dept
1	ABC
2	BBC
3	ABC

[smarthp29]

You would require 1 calculated column to tag the latest department in the billing table and based on that you can create your measure.

Lastest Dept Tagging = IF(Billing[Dept] = CALCULATE(MAX(CurrentUser[Dept]),ALLEXCEPT(CurrentUser,CurrentUser[Employee])),TRUE(),FALSE())

Total Amount = IF(DISTINCTCOUNTNOBLANK(EmployeeYTD[Team])>1,CALCULATE(SUM(Billing[Amount]),Billing[Lastest Dept Tagging]=TRUE()),SUM(Billing[Amount]))

Output Table

EmployeeYTD Table

Billing Table

CurrentUser Table

[rajbi2020]

The first cust column help to define current dept .. if true or false

then there is a realtionship between teammaster and EmployeeYTD ,So when i select team 1 from the slicer then the count is always 1 for that team .. .. . 

TEAM ID	DEPT
1	ABC
2	BBC

So I tried something like below .. .. and got the current dept amount correctly .. but when i selected the second team I should get the remaining amount ..Since the first  count condition is satisfied its is not going to else if i select the old team.

CALCULATE(DISTINCTCOUNT(EmployeeYTD[team]),CROSSFILTER(DEPT[TEAM_ID],EmployeeYTD[team],None)) 

Since i set used crossfilter none its get me all the user list which is again wrong.. I am not sure if I can use cross filter or something else 

please suggest

[smarthp29]

@rajbi2020 

I am unable to understand your full requirements. Let me know what output are you expecting. TeamID comes from TeamMaster Table.

Attached screenshot of the output i get based on my TeamID Slicer.

[rajbi2020]

If the employe doesnt change the team then we should get the total amount ignoring the dept he worked . 

So for employee 1 amount is 36 -team 1

So for employee 2 - If we select team 1 (old team) then we need to get the sum(all dept except BBC which is current)

     if we select team 2 then we need to get only amount related to BBC ie 30 ) below are the table output 

Sorry for confusing .            

EMPLOYEE	Amount
1	36
2	36 (EXCEPT BBC DEPT)

If selected 22 

Employee	Amount
2	30(only BBC)

[rajbi2020]

I will try now thanks .