Need help to sum depending on mutliple conditons.

Question

Hi All, I need some help ..&nbsp;&nbsp;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 .&nbsp;&nbsp;1 Senario&nbsp;&nbsp;if employee never change the team get sum (amt)&nbsp; irrrespective of the dept .2.&nbsp;&nbsp;If employee changed the teams from 11 to 22&nbsp; and&nbsp; we select 22 which is currect team . then only display his current dept sum(amt) ie BBC 20 and If we select 11 then&nbsp; display sum(other amount) ie leave his current team dept amout . Ie&nbsp;&nbsp;sum(amount) not inclusive of DEPT BBC&nbsp;I am trying to create a measure for this . using calculate and filters .. but i am not sucessfullActual = CALCULATE(SUM(Billed[AMOUNT]),FILTER(Employeeytd ,Employeeytd[DEPT]=FIRSTNONBLANK(CurrentUser[DEPT],TRUE())))&nbsp;1&nbsp; Billing tableEMPLOYEEperiod(year&amp;month)DeptAmount1201912ABC101202001ABC121202002BBC142201912ABC102202001ABC122202002DBF142202003BBC203201912CDE192202001BBC10&nbsp;&nbsp;&nbsp;2 Employee ytd TABLEEMPLOYEEperiod(year&amp;month)team120191211120200111120200211120200311220191211220200122220200222320191244&nbsp;3 .Current user table latest month.employeedept1ABC2BBC3ABC&nbsp;&nbsp;

smarthp29 · Answer

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 · Answer

I will try now thanks .

rajbi2020 · Answer

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 · Answer

rajbi2020&nbsp;&nbsp;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 · Answer

&nbsp;If the employe doesnt change the team then we should get the total amount ignoring the dept he worked .&nbsp;&nbsp;So for employee 1 amount is 36 -team 1So for employee 2 - If we select team 1 (old team) then we need to get the sum(all dept except BBC which is current)&nbsp; &nbsp; &nbsp;if we select team 2 then we need to get only amount related to BBC ie 30 ) below are the table output&nbsp;&nbsp;Sorry for confusing .&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;EMPLOYEEAmount136236 (EXCEPT BBC DEPT)&nbsp;If selected 22&nbsp;EmployeeAmount230(only BBC)

Forum Discussion

Need help to sum depending on mutliple conditons.

5 Replies

Related content

COnditonal Column

Complex Conditonal Formatting

Circular dependency

Working with mutliple nested notebooks

Dax query dependency

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

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