Reply
Member
Posts: 64
Registered: ‎02-10-2017

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!

 

Super Contributor
Posts: 3,303
Registered: ‎08-14-2016

Re: Issue Replicating Data

Hi @New2PowerBI,

 

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

 

Regards,

Xiaoxin Sheng

Highlighted
Member
Posts: 64
Registered: ‎02-10-2017

Re: Issue Replicating Data

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!!

Super Contributor
Posts: 3,303
Registered: ‎08-14-2016

Re: Issue Replicating Data

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

 

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Member
Posts: 64
Registered: ‎02-10-2017

Re: Issue Replicating Data

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])))))).

Member
Posts: 64
Registered: ‎02-10-2017

Re: Issue Replicating Data

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. 

Member
Posts: 64
Registered: ‎02-10-2017

Re: Issue Replicating Data

I could not get the second measure "Current Group" to work, so I tried this:

 

Current Group = CONCATENATEX(FILTER(ALLSELECTED('REQUIREMENT REPORT 04192017'),[Employee Number]=MAX([Employee Number])&&ISERROR(FIND("*",'REQUIREMENT REPORT 04192017'[Group Assigned To]))),[Group Assigned To],",")

 

That didn't yield an error; I felt I had one too many [Group Assigned To] however, my table is only counting distinct for some values, but not others.  I also noted the Distinct Cost looks way off (needless to say, I need to work on this some more). 

 

Here is what the Power BI Table noted AFTER putting in the two measures and calculated field:

Employee NameEmployee NumberGroup Assigned ToEstimated Yearly CostDistinct CostCourse TitleCurrent Cost
ABRAHAMS, EMILIO A.201099759*Authorized Employee$65$2,271.752.10 EH&S Work Permit (Initial) 
ABRAHAMS, EMILIO A.201099759*Co Emp w/ Field Duties$65$2,271.752.10 EH&S Work Permit (Initial) 
ABRAHAMS, EMILIO A.201099759* Co Technician$65$2,271.752.10 EH&S Work Permit (Initial) 
ABRAHAMS, EMILIO A.201099759QAT-OQ PV 10$65$2,271.752.10 EH&S Work Permit (Initial) 
ABRAHAMS, EMILIO A.201099759*Authorized Employee$65$2,271.751.20 Excavation Standard 
ABRAHAMS, EMILIO A.201099759*Co Emp w/ Field Duties$65$2,271.751.20 Excavation Standard 
ABRAHAMS, EMILIO A.201099759* Co Technician$65$2,271.751.20 Excavation Standard 

ABRAHAMS, EMILIO A.

201099759QAT-OQ PV 10$65$2,271.751.20 Excavation Standard$65
Super Contributor
Posts: 3,303
Registered: ‎08-14-2016

Re: Issue Replicating Data

[ Edited ]

Hi @New2PowerBI,

 

I modify the formula based on new sample data, current these formulas can get the valid records and calculate the valid total of current employee(current group).

 

Is valid = ISERROR(FIND("*",MAX(Sheet4[Group Assigned To])))

Valid Group = if([Is valid],MAX(Sheet4[Group Assigned To]))

Valid Cost = if([Is valid], MAX([Estimated Yearly Cost]))

Valid Total(Current Group) = if([Is valid], SUMX(FILTER(ALL(Sheet4),[Employee Number]=MAX([Employee Number])&&[Group Assigned To]=MAX([Group Assigned To])&&[Is valid]),[Estimated Yearly Cost]))

 

Capture.PNG

 

Notice: I used the "*" to check if the current group is valid, use current group and employee to calculate total cost.

 

Regards,

XiaoxinSheng

Attachment
Member
Posts: 64
Registered: ‎02-10-2017

Re: Issue Replicating Data

Thanks; my results aren't coming out as planned.  I'll have to look at this closer and spend more time on this.  I've spent a lot of time on this one already and I'm thinking maybe I need to create multiple tables and separate what I want instead of doing a massive table.  Esssentially I want to know people, training assigned, cost and groups tied to it all.  I think longer term, it may be a better approach.