Suming two Columns from Different Tables - With Same Column Name

[Mavericks334]

Hi,

I have Different Tables, 1. Voluntary Attrition. 2 InVoluntary Attrition. 3 Business Units

Each had dept ID' Column, Month Column. Below table is one for Voluntary Attrition.

[TABLE="width: 242"]
<COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 67"]Dept Id[/TD]
[TD="class: xl66, width: 64"]Oct'09[/TD]
[TD="class: xl66, width: 64"]Nov'09[/TD]
[TD="class: xl66, width: 64"]Dec'09[/TD]
[TD="class: xl66, width: 64"]Jan'10[/TD]
[/TR]
[TR]
[TD="class: xl67"]3310ED[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3310FL[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67"]3310HD[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3310PZ[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


Similar table for involuntary attrition. I need to combine the both the Tables and get a consolidated number according to the month and Dept Id.

Regards,
Renato.

 

[Robert Mika]

Hi,

I have Different Tables, 1. Voluntary Attrition. 2 InVoluntary Attrition. 3 Business Units

Each had dept ID' Column, Month Column. Below table is one for Voluntary Attrition.

[TABLE="width: 242"]
<TBODY>[TR]
[TD="class: xl65, width: 67"]Dept Id
[/TD]
[TD="class: xl66, width: 64"]Oct'09
[/TD]
[TD="class: xl66, width: 64"]Nov'09
[/TD]
[TD="class: xl66, width: 64"]Dec'09
[/TD]
[TD="class: xl66, width: 64"]Jan'10
[/TD]
[/TR]
[TR]
[TD="class: xl67"]3310ED
[/TD]
[TD="class: xl69, align: right"]1
[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69, align: right"]1
[/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3310FL
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl67"]3310HD
[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3310PZ
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


Similar table for involuntary attrition. I need to combine the both the Tables and get a consolidated number according to the month and Dept Id.

Regards,
Renato.

Are your data in the same Sheet? On a different one?If the latest are they on the same position on each tab?

 

[Mavericks334]

in a different sheet and and the latest are on the same position of each tab

 

[Robert Mika]

in a different sheet and and the latest are on the same position of each tab

Try this assuming that the heading in both sheets are in the same position:

Excel 2010
	A	B	C	D	E
1	Dept Id	Oct'09	Nov'09	Dec'09	Jan'10
2	3310ED	1		1
3	3310FL				1
4	3310HD
5	3310PZ
Voluntary Attrition

Excel 2010
	A	B	C	D	E
1	Dept Id	Oct'09	Nov'09	Dec'09	Jan'10
2	3310FL	1		1
3	3310ED				1
4	3310HD
5	3310PZ
InVoluntary Attrition

Excel 2010
	A	B	C	D	E	F	G	H	I
1		Oct'09	Nov'09	Dec'09	Jan'10
2	3310ED	1	0	1	1				Voluntary Attrition
3	3310FL	1	0	1	1				InVoluntary Attrition
4	3310HD	0	0	0	0
5	3310PZ	0	0	0	0
Summary

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$a$2:$a$20"),$A2,INDIRECT("'"&list&"'!"&SUBSTITUTE(CELL("address",($A$1,B$2:B$20)),"$A$1,",""))))

Named Ranges
Name	Refers To	Cells
list	=Summary!$I$2:$I$3

Copy formula down and accross
You can hide the 0 using Custom Format:

Right Click on the Cell--Format Cells-->Number-->Custom.
Under Type enter:
[=0]"";General

 

[Mavericks334]

Thanks Robert, this would work in the regular excel, i need this work in Powerpivot, i have the above formula already for regular excel, i am trying to use powerpivot to get the information which i would later compute monthly and 12 month moving average.

 

[Robert Mika]

Thanks Robert, this would work in the regular excel, i need this work in Powerpivot, i have the above formula already for regular excel, i am trying to use powerpivot to get the information which i would later compute monthly and 12 month moving average.

Aplogize maverick.

I have posted in wrong forum.
Hope someone else will help you

 

[Mavericks334]

Hi I tried the below DAX mesure to calculate the two columns
=IF(HASONEFILTER(BU[Dept Id]),CALCULATE(VALUES(TERM[Oct 11]),BU)&CALCULATE(VALUES(XOFT[Oct 11]),BU))

I am able to add the columns accordingly however my problem is that if i have more than 12 columns in each table i need to write 12 such measures. I need to create something dynamic that would automatically calculate the respective columns accordingly. Also

When i create the pivot to get the data it only gives me by the id not the name of the dept even after created a relationship between these tables.

 

[Laurent C]

You have not really asked a question, but I assume you want to know how you can work with these data in PowerPivot.

My answer: you cannot (at least not in a meaningful way).

You will have to import them in a correct format {Dept ID, Month, First Day Of Month}

 

[Mavericks334]

Hi Laurent,

My question is how do i combine two columns where they have the same name in from different table. The above format is how my data comes every month. The Dept id and the current month as the column name, i need to combine both these tables together and then i would use it to get a moving average for 12 months.
So each month {Dept Id Oct Nov Dec} so on would be added to get the data.

I tried to use the data in three columns dept id Month and Attrition, how ever it does not work because i am unable to create a relation ship between both these tables. Hopeful this could be done with a DAX, otherwise would need MDX to see if it would work.

 

[Laurent C]

My advice to you is to transform this data in a PowerPivot-friendly format, and make sure your table has a column for Dept ID, one for the month, and one for your metric.

You can do this when you import your data every month, either with VBA, or SQL, or Excel formulas, ...

I also suggested you add a column with the first day of the month, so you could link your data to your Date table. Also link the Dept ID to the appropriate table, and from there on, it should be easy to do your DAX expressions.