Forum Discussion

rajbi2020's avatar
rajbi2020
Frequent Visitor
6 years ago

Need help to sum depending on mutliple conditons.

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 tea...
rajbi2020's avatar
rajbi2020
Frequent Visitor
6 years ago

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 IDDEPT
1ABC
2BBC

 

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's avatar
smarthp29
Icon for Helper I rankHelper I
6 years ago

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's avatar
    rajbi2020
    Frequent Visitor
    6 years ago

     

    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 .            

     

    EMPLOYEEAmount
    136
    236 (EXCEPT BBC DEPT)

     

    If selected 22 

    EmployeeAmount
    230(only BBC)