sort of lookup/measure question!!!

[masplin]

I have a table "transactions" which includes a field "transaction month" formatted so its 1st of each month. I have another table "Webstats" with website vistor numbers in a column "web visitors" organised by "Visitor month", also formatted 1st of each month. I have created a realtionship between "transaction month" and "visitor month". I can ceate a column in transactions using retalte that gett he right web vistors for the mnoth of each transaction so I know the RELATE is working.

I have a pivot table with "transaction month" as the row label and assorted columns. I want one of the columns to be the vistors in that month taken from the web stats table. I have tried everything I can think of with no success.

If I use a measure RELATED(web Stats[web vistors]) I get the total visitors for all months repeated in each line not just the vistors for that transaction month. I was expecting the transaction month filter to just pick the relevant month. I have tried using various filters to make transaction month=vistor month, but I just get errors.

I'm sure this is trivial for you smarter folk out there so any hep appreciated.

Mike

 

[ruve1k]

Why don't you put Visitor Month as row labels instead of Transaction Month?

 

[masplin]

Well switching the transaction month to visit month for row labels seems to work, but I have no idea why? can you explain???

My next problem is one of the columns I'm after is registered users/new vistors to get website conversion. I have a 3rd table "users" where a count of User ID for a given "Reg Month" gives me user registering the month. Now I can't create a relationship between Reg Month and Visit Month as visits already related to Trans Month. If I want to add a column for registered users usually it is just click on User ID and change to count, but agian this gives me the total ever not the month as doesn't understand the row label.

Trying a measure like this just throws an error as says the filter can only have one column.

=CALCULATE(count(users[User ID]),users[Reg Month]=WebStats[Visit Month])

I basically have a date which is sort of "ACTIVITY MONTH" , but it appears in 3 tables with 3 differnet names. Is there anyway of bringing this data together on one pivot table?

Thanks Mike

 

[ruve1k]

Please post samples of each table and clearly explain the relationship(s) between them so that we can have a meaningful conversation instead of me shooting in the dark. Thanks.

 

[masplin]

Hope this makes more sense
User table

-- removed inline image ---


Using the "visit month" column in the pivot table even though titled transactions. New loads is calc by counting in transactions table, New vistors now coming form webstats correctly. Registrations is count of user ID in user table for the month in question and then I can divide by new visitors to get %conversion. Has to come from the User table as they won't appear in the tranasction table until they do something.

Thanks again

 

[masplin]

sorry the tables didnt paste in. Had a hunt around and there was a tool HTML maker for doing this but currently unavailable. Is there any other way for me to post the table structure please?

 

[ruve1k]

The HTML Maker was available when I tried. Personally, I use the JMT Excel Utilities add-in which has a tool to export (copy) a range to HTML.

 

[masplin]

I'm sorry I'm being extremely stupid. I got the utility installed and selected the cells in the pivot table and copy to html. I'm too stupid to be able to work out how to get the html off the clipboard and into the post!

Even so that doesn't help me to show you the structure of the 3 underlying powerpivot tables as can only select the whole columns. If I can get the above to work I can mock up in excel if that is the only way to do it.

Thanks Mike

 

[ruve1k]

Just paste it in and then click the Preview Post button.

 

[masplin]

ok got it for excel. Is there anyway to show the powerpivot table layout as trying to explain the relationships?