scratching my head about RELATED calculation

[masplin]

I have 2 tables

Transactions: Transaction Day,Transaction Month (jan-Dec), Amount (£)
Seasonality: Month (Jan-Dec), Seasonal Factor (80-130%)
I have created a relationship between transaction month and Seasonality[Month]

I can add a new column to the transaction table transaction[amount]/RELATED(seasonality[seasonal factor]) and works fine.

I wanted to avoid a new column and have this as a measure in the pivot table. I created a pivot table with transaction month as rows. When I added Seasonal factor I expected that it would filter the seasonality table by the month due to the relationship and therefore give me the seasonal factor for each month. However I just get 12 for the sum. I tried using various forms of RELATED, but it just says doesn't have any relationships in the current context.

Any advice appreciated.

Mike

I'm a bit confused why the relationship works in the powerpivot table, but not in a pivot table.

 

[pete234]

Did you figure this out? I think it is getting confused if the tran table has days and the relationship is at the month level and you don't have a date table between them. It sounds like it's just counting the month line giving you twelve. When it rolls in the pivot table I think the results would be skewed, as % is always a pain in pivots in my experience. Anyway, curious if you made progress.

 

[masplin]

Hi Pete. I do actually have a date table. The transaction table starts with date dd/mm/yy hh:mm:ss. I then create MMM, YYYY, DD-MMMM formats. DD-MMMM is related to my dates table. Works fine for other pivots just not this one. What I dont understand is that the RELATED function in the Transaction table works fine and the Seasonailty table only contains MMM. The implication bewing that when it sees JAN in transactions it understands to look up JAN in seasonality.

My undertstanding was when you put JAN in the rows it takes that as a filter on the measure so in theory should work. Maybe I could try doing the seasonailty on a dialy basis, but seems strange it would work.

Mike

 

[Laurent C]

RELATED only works when you have a row context. This is the case when you work at the table level, or when you work within a function that creates a row context (SUMX, COUNTX, ...).

 

[masplin]

right but I thought I have a row context. The row has the transaction field MMM and this the field that is related to the MMM field in seasonality.

 

[JavierGuillen]

By default, context propagates from the lookup table to the base table. In a calculated column, you *can* use RELATED from the base table an reach out to the lookup table.

As a measure, if you place base table attributes in the pivot table rows and want to propagate the context to the lookup table, you must reverse the default way in which context propagates by using CALCULATE:

try the following:

IF(HASONEVALUE( transaction[month]) ,
CALCULATE( VALUES( seasonality[seasonal factor] ) , transaction )
)

 

[masplin]

Hi Javier. Transactions is the lookup table I asume as it has them any vlaues and seaosnaitly is the base table as it has unique values. I placed the lookup (transaction) MMM values in the rows not the base table values. I can try your suggestion, but think I have it the normal way round?

 

[JavierGuillen]

the lookup table is that one that has unique values in the key column (the one used to relate to another table).

from what you say, i would assume seasonality *is* the look up table. One easy way to verify is to go to the 'manage relationships' option in the powerpivot window design tab and select the relationship in question. the second table (the one in the bottom) is the lookup table.

 

[Mavericks334]

Hi Javier,

I tired creating the relationships the way you are using in your table. During the creation of relationships, a warning comes up which says the relationship cannot be created in the requested direction.when you click create, the direction of the relationship will be reversed.

You could check your relationship once again. That could be the problem.