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

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)