comparison chart using powerpivot with same set of two period data

[pkseenu]

Hi,

I have two tables in excel for Q1 and Q2 with same column headings Region, Product name, version details, OS type, Total count. Data contains dubplicate values (i.e. in region field same region name will appear more times same for other columns). I want to compare these two table data and create a comparison chart for each product....

Thanks in Advance!!

 

[pete234]

Hi,

I have two tables in excel for Q1 and Q2 with same column headings Region, Product name, version details, OS type, Total count. Data contains dubplicate values (i.e. in region field same region name will appear more times same for other columns). I want to compare these two table data and create a comparison chart for each product....

Thanks in Advance!!

You need a middle table with unique values. Let's assume you want Region-Area or some such as the row grouping.
In order to build the relationship, you have to have the unique region table in the middle. Then make the other two tables relate to this one by Region or what you need. You might also consider building a fiscal table based on the data you have. For right now, Q1 and Q2 would be ok if it's a one time deal. When you are done, you'd have region on the rows and Q1 and Q2 on value rows for a side by side comparison. It's fairly straightforward so you might have figured it out by now.

 

[pkseenu]

You need a middle table with unique values. Let's assume you want Region-Area or some such as the row grouping.
In order to build the relationship, you have to have the unique region table in the middle. Then make the other two tables relate to this one by Region or what you need. You might also consider building a fiscal table based on the data you have. For right now, Q1 and Q2 would be ok if it's a one time deal. When you are done, you'd have region on the rows and Q1 and Q2 on value rows for a side by side comparison. It's fairly straightforward so you might have figured it out by now.

I have tried this method i.e. created one more table with unique region list and tried to create relationship using that table with others but I am getting the same error "fields containing one or more repeat values"

 

[Carlos77]

Hi... Sorry for hijacking the thread but since I am facing a similar challenge, I thought why not post my problem here.

I am trying to compare date from three tables which are identical but have data from different periods of time. For example my tables looks like this and I have three tables with same product codes for three different time periods, lets say years.

[TABLE="width: 430"]
<tbody>[TR]
[TD]Product Code
[/TD]
[TD]Material Code[/TD]
[TD]Supplier Code[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] $ 5.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] $ 4.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD] $ 6.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD] $ 3.00
[/TD]
[/TR]
</tbody>[/TABLE]

Now what I want to do is to compare prices from different suppliers for the these products in all 3 years. I have made a lookup table for product code only and have linked all three tables to the new products table and I am able to extract price information from all three tables but when I try and include material code in the report it just simply gets repeated for each product code.

In excel, I do a lot of vlookup and index/match to pull data from different similar tables into one central table to do this analysis but its a lengthy and tedious process.

Please advise what could be the best way to possibly compare data like this using powerpivot.

 

[scottsen]

You mention wanting to compare by year -- in this case, I would expect a lookup table of years (or better, a "real" calendar table with all dates). Do you have such a thing?

 

[Carlos77]

In my case the exact calendar is not that effective or necessary at all. Infact our fiscal calendar is setup very differently. Our fiscal year starts from December 1 each year and end on 30th November next year. Also I am only giving an example of year but our price data is divided in two halves of every year. So right now I have the price table for first half of 2014, 2nd half of 2014 and then 3rd half of 2015.

What I want to do is to link all three tables in a way in power pivot that I can simply pull similar columns from each and just compare the prices. After this another level is that sometimes I also want to see if a price of a product in any given season was in what material. So it gets very complicated. Right now what I am able to do is link tables in a way that two are fact tables and one is lookup table (linking product codes in all three but making one table a lookup table) and then use the columns from lookup table in power pivot row field and prices from all three tables in the values field. After that I am using calculated fields to calculate difference in %age.

What other options do I have?

 

[scottsen]

What you are doing is correct, and you need to extend that concept to a Calendar table (even if its a "custom" one)

 

[Carlos77]

Okay then... The next problem is that my price tables don't have any column showing dates or months or anything related to time frame... It's just that I know for sure that a specific table is for a specific time period... For a calendar lookup table to work I think I should have this data available in my main tables to link to and this means I will have to manually add another column in each of the table.

Am I thinking right?

 

[scottsen]

Hard to see without seeing the model.

In general, if I have Sales across multiple years in different files... I try to combine them into 1 table in Power Pivot (typically by using Power Query to append them). Otherwise I am going to end with hard coded measures Sales2013 and Diff2014to2013 or whatever, which... will kinda stink in 2015

 

[TimRodman]

I agree with Scott. Using Append in Power Query (pictured below) would be the way to go. You could just add a column and hard code a date for each data source.