Many to many date relationship

[chaserracer83]

I have two tables tbl_Transaction and tbl_SalesCall. Tbl_Transaction contains information on all transactions (Date, charges...). Tbl_SalesCall contains information on all sales calls (Date...). I want to make calculations such as the total charges in tbl_Transaction based on a date in tbl_SalesCall. The date field is not unique in either table. I have a third table tbl_Dates which contains all my contiguous dates.

I have started by creating a relationship between tbl_SalesCall/tbl_Dates and tbl_Transaction/tbl_Dates (I would like to note that PowerPivot puts tbl_Dates in the lookup table column. I have looked over other forums to try and understand what I am doing. and in those forums the many table is always in the lookup table. I am not sure if this is why I am having issues, but I don't know how to address that). Once the relationships are created I test things out by starting with the following calcualted column equation in tbl_SalesCall:

=SUMX(RELATEDTABLE(tbl_Dates),RELATED(tbl_Transaction[Charges]))

I get an error that tells me tbl_Transaction[Charges] doesn't exist or doesn't have a relationship in the current context.

I am a novice to PowerPivot and can't figure out what I am doing wrong. Any guidance would be greatly appreciated.

Thank you,
Chase

 

[MD610]

Chase - I think you are close but might be over complicating things for yourself with the SUMX function.

First off, in a one to many relationship, PowerPivot will only let you create in the proper direction. The many table can't be the lookup table because then when you try to look something up, it would have more than 1 value. The relationships in PowerPivot are similar to a vlookup in regular excel.

With the relationships you have in place, create a new pivot. Drop the date field from tbl_Dates into the Row Labels. Drop the Date field from the tbl_SaleCall into the Values window. Make sure your are getting a Count of Dates from the Salecall Date. This is now just showing you the Dates that you had sales calls in the pivot along with a count of the number of calls that date. Finally, drop the Charges field from the tbl_Transactions into the Values window below the Count of Dates. Make sure it is saying Sum of Charges. Now you should be seeing the Charges summed up by date of call in your pivot. If there was a call one day but no charges, the Sum of Charges field will be blank. If you just want to see dates where there were Charges, remove the Count of Dates field from Values and you will only see Dates where Charges occurred.

Hope this gets you what you need.

 

[chaserracer83]

Chase - I think you are close but might be over complicating things for yourself with the SUMX function.

First off, in a one to many relationship, PowerPivot will only let you create in the proper direction. The many table can't be the lookup table because then when you try to look something up, it would have more than 1 value. The relationships in PowerPivot are similar to a vlookup in regular excel.

With the relationships you have in place, create a new pivot. Drop the date field from tbl_Dates into the Row Labels. Drop the Date field from the tbl_SaleCall into the Values window. Make sure your are getting a Count of Dates from the Salecall Date. This is now just showing you the Dates that you had sales calls in the pivot along with a count of the number of calls that date. Finally, drop the Charges field from the tbl_Transactions into the Values window below the Count of Dates. Make sure it is saying Sum of Charges. Now you should be seeing the Charges summed up by date of call in your pivot. If there was a call one day but no charges, the Sum of Charges field will be blank. If you just want to see dates where there were Charges, remove the Count of Dates field from Values and you will only see Dates where Charges occurred.

Hope this gets you what you need.

Thank you for the reply. This doesn't exactly fix my situation. I kinda simplified things for the sake of the post...

Each table has several criteria I will be evaluating. A sales call is for a particular customer so I want to filter out transactions based on the account number for the sales call record. A sales call could be in regards to a particular region or all regions. A sales call could be for one product or all products... essentially I want to be able to look at each record in tbl_SalesCall and sum up total charges in tblTransaction for records that meet the criteria. Is there anyway to set something like this up in PowerPivot?


P.S. I don't see how I could easily relate these two data sets into third normal formation so I am basically looking for a work around.

Thank you

 

[MD610]

Do both tables contain the same fields such as Account#, region, Product, etc? If not, what fields besides date do they have in common?

A data sample or a list of each tables fields might help with pinpointing a solution.

 

[Laurent C]

"and sum up total charges in tblTransaction for records that meet the criteria. Is there anyway to set something like this up in PowerPivot?"

Yes, there is: it is called a pivot table and/or a data model

If I understand this correctly, you have already linked both tables to your Date table, which is the lookup table in this relationship. B

Just build a lookup table for each attribute that is common to both tables, and specify the relationship and you can simply define your measure like this:
[Total charges] :=
=SUM(tbl_Transaction[Charges])

The RELATED function requires a row context, which is not provided in a measure, except in the second argument of an iterating function (SUMX, COUNTX, ...). Most of the time you will not need it in a measure anyway.

Please also note, that this is not what is called a "many-to-many" relationship.