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 f...
Need Help
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

    • New2PowerBI's avatar
      New2PowerBI
      Helper III
      9 years ago

      Thanks, I'm attempting to enter the Measures, here is the first one:

       

      Distinct Cost = CALCULATE(SUM('REQUIREMENT REPORT 04192017'[Estimated Yearly Cost]),FILTER(ALLSELECTED('REQUIREMENT REPORT 04192017',ISERROR(FIND("*",'REQUIREMENT REPORT 04192017'[Estimated Yearly Cost]))))

       

      It gave me an error; here is what it notes:

       

      The syntax for 'REPORT' is incorrect. (DAX(CALCULATE(SUM('REQUIREMENT REPORT 04192017'[Estimated Yearly Cost]),FILTER(ALLSELECTED('REQUIREMENT REPORT 04192017',ISERROR(FIND("*",REQUIREMENT REPORT 04192017[Estimated Yearly Cost])))))).

      • New2PowerBI's avatar
        New2PowerBI
        Helper III
        9 years ago

        Please disregard previous message; I went back and corrected it, I saw where I had messed it up. 

         

        The updated one looks like this:

         

        Distinct Cost = CALCULATE(SUM('REQUIREMENT REPORT 04192017'[Estimated Yearly Cost]),FILTER(ALLSELECTED('REQUIREMENT REPORT 04192017'),ISERROR(FIND("*",'REQUIREMENT REPORT 04192017'[Group Assigned To]))))

         

        ...and it worked!  Going to do the next Measure and Calculated Column next.