Forum Discussion

New2PowerBI's avatar
New2PowerBI
Helper III
9 years ago

Issue Replicating Data

Would like some help; I'm trying to assess how much training our employees receive, and associated costs.

 

When I pull out the data, I have a "course name" and associated information; however, I found where the rows are being duplicated.  That is because I have another field called "team"...so let's say you are part of three different "teams" and each of those "teams" requires the same "course name", the employee only takes the course once, not three times.  But from a calculation perspective, it's counting the cost of the course 3x.  I'd like to clean up the data but don't want to remove "teams", because it's important to know for slicing/filtering purposes on visuals.  Any tips?

 

I'm new to Power BI and learning...it sounds like I need to list all the courses...then have a column with another table that is generated based on the teams associated to that course??? I can see it in my head, can't seem to do it inside application.  Thanks in advance for your help!

 

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi New2PowerBI,

     

    It will be help if you share some sample data and detail informations.

     

    Regards,

    Xiaoxin Sheng

    • New2PowerBI's avatar
      New2PowerBI
      Helper III
      9 years ago

      Here is a sampling of some of the information:

       

      EMPLOYEE NAMEEMPLOYEE NUMBERTRAINING NAMEFREQUENCY (MONTHS)ESTIMATED ANNUAL COSTTEAM / GROUP NAME
      ABRAHAMS, EMILIO A.201099759229.129 Excavation Practices12$65*Co Emp w/Field Duties
      ABRAHAMS, EMILIO A.201099759229.129 Excavation Practices12$65*Co Technician
      ABRAHAMS, EMILIO A.201099759229.129 Excavation Practices12$65QAT-OQ PV 10
      ABRAHAMS, EMILIO A.201099759229.129 Excavation Practices12$65*Authorized Employee

       

      Our employees are assigned to "Teams" based on what work they have to perform.  In the above; the employee is assigned to 4 Teams, however, in our training system, it "knows" that the employee isn't supposed to take the training 4 times, the employee just needs to take it once. 

       

      When I did my visualizations, I quickly realized the duplication; so the sum of training cost is counting the same course 4 times.  I just want it to count it once, BUT, I would like to know, on a related visual what "Teams" this employee belongs to; so I don't want to lose the information. 

       

      Hope this helps.  Thanks!!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi New2PowerBI,

         

        You can try to use below formula to get the distinct cost:

         

        Measures:

        Distinct Cost = CALCULATE(SUM(Sheet3[ESTIMATED ANNUAL COST]),FILTER(ALLSELECTED(Sheet3),ISERROR(FIND("*",Sheet3[TEAM / GROUP NAME]))))

         

        Current Group = CONCATENATEX( FILTER(ALLSELECTED(Sheet3),[EMPLOYEE NUMBER]=MAX([EMPLOYEE NUMBER])&&ISERROR(FIND("*",Sheet3[TEAM / GROUP NAME]))),[TEAM / GROUP NAME],",")

        Calculate column:

         

        Current Cost = if( ISERROR(FIND("*",Sheet3[TEAM / GROUP NAME])),[ESTIMATED ANNUAL COST],BLANK())

         

         

         

        Regards,

        Xiaoxin Sheng