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

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

    I will try now thanks .

  • 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)