Dynamic measure Power Pivot

[ranco11]

Hello everyone,

I have the following problem:

I have twoinput sheets in Power Pivot, one for data in 2013 and one for data in 2015.
Both sheets contain data for e.g. the retention rate of customers perproduct. Both data sets contain similar products.
For data in table with 2013 data I created a measure which calculates theaverage retention rate. In the output power pivot, I can easily display theaverage retention rate per product.

Now I wouldlike to show the retention rate per product from 2013 in the 2015 power pivottable for the same product as well (to compare results).

If I createin the 2015 table a new measure which simply links to the name of the retentionrate measure from 2013 I get as a result the same value shown (the averageretention rate of all products in 2013) for each product in 2015.
I tried to usecalculate or summarize function but nothing seems to work.

How can Idynamically (based on products shown in the 2015 power pivot table) show theresults of the measure from 2013 based on the product shown in the power pivot2015 table?

Thanks alot in advance for your help!
Martin

 

[Cool blue]

If the left hand column in your pivot table is product, for example, then you need to create a product dimension table. This is a table of all distinct products in both periods. Then add it tot the data model and link it to both of your fact tables.

If one of your two fact tables already has a complete and distinct column of products, then you can just use that as the product dimension table and link it to the other fact table.

Then add a year column to each of your fact tables (it will be the same value for every row).

then create a year dimension table and link that to your fact tables

Then in your pivot table, use the product column of the product dimension table as your left hand column and the year column in the year dimension table as the columns and write your measures to reference product and year dimensions.