Issue Replicating Data

[New2PowerBI]

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!

[v-shex-msft]

Hi @New2PowerBI,

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

Regards,

Xiaoxin Sheng

[New2PowerBI]

Here is a sampling of some of the information:

EMPLOYEE NAME	EMPLOYEE NUMBER	TRAINING NAME	FREQUENCY (MONTHS)	ESTIMATED ANNUAL COST	TEAM / GROUP NAME
ABRAHAMS, EMILIO A.	201099759	229.129 Excavation Practices	12	$65	*Co Emp w/Field Duties
ABRAHAMS, EMILIO A.	201099759	229.129 Excavation Practices	12	$65	*Co Technician
ABRAHAMS, EMILIO A.	201099759	229.129 Excavation Practices	12	$65	QAT-OQ PV 10
ABRAHAMS, EMILIO A.	201099759	229.129 Excavation Practices	12	$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!!

[v-shex-msft]

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]

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]

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. 

[New2PowerBI]

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 Name	Employee Number	Group Assigned To	Estimated Yearly Cost	Distinct Cost	Course Title	Current Cost
ABRAHAMS, EMILIO A.	201099759	*Authorized Employee	$65	$2,271.75	2.10 EH&S Work Permit (Initial)
ABRAHAMS, EMILIO A.	201099759	*Co Emp w/ Field Duties	$65	$2,271.75	2.10 EH&S Work Permit (Initial)
ABRAHAMS, EMILIO A.	201099759	* Co Technician	$65	$2,271.75	2.10 EH&S Work Permit (Initial)
ABRAHAMS, EMILIO A.	201099759	QAT-OQ PV 10	$65	$2,271.75	2.10 EH&S Work Permit (Initial)
ABRAHAMS, EMILIO A.	201099759	*Authorized Employee	$65	$2,271.75	1.20 Excavation Standard
ABRAHAMS, EMILIO A.	201099759	*Co Emp w/ Field Duties	$65	$2,271.75	1.20 Excavation Standard
ABRAHAMS, EMILIO A.	201099759	* Co Technician	$65	$2,271.75	1.20 Excavation Standard
ABRAHAMS, EMILIO A.	201099759	QAT-OQ PV 10	$65	$2,271.75	1.20 Excavation Standard	$65

[v-shex-msft]

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

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

Regards,

XiaoxinSheng

[New2PowerBI]

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.