CODEWITHSUNDEEP
excelms-officeexcel-2010excel-2013powerpivot
user2768380
user2768380

Reputation: 151

query in a powerpivot table to do a group by

I have a table called "purchase" with the following data:

year   month    time    day_week    name    provider    service    purchase    revenue

I am using this data to make a pivot table. Then I have another table called "active":

 name    provider    service    description    month    year    number_active

I used this data to create a separate pivot table.

Now I need a third pivot table with something like:

 number_active/revenue

and slice it vertically to by year. The problem is that I cannot create a relationship between the table because in Purchase I have the services by day and hour and in Active I have the services by month. I think I need to create another table that is a view of Purchase grouped by month.

I tried the following :

 home ->  From Database   ->  From analysis services or PowerPivot

But i have a error:

http://imgur.com/KUeuUc0

I'm not sure where to go from here.

How can I make this view grouped by month?

Upvotes: 0

Views: 931

Answers (1)

mmarie
mmarie

Reputation: 5638

I don't think you need to create a new view of summarized data as a source. You just need to be able to summarize by date, and you can do this by adding a date dimension.

You can download one for free from the Windows Azure marketplace.

Once you have your date table, add a calculated column to the Active table in your Power Pivot model called Date that is =[Month]&"/01/"&[year]. Then change the data type to date.

Then do the same thing to your Purchase table. Next, create relationships from Active[Date] to Date[DateKey] and from Purchase[Date] to Date[DateKey].

You could make all of this go down to the time level and related it there instead (Power Pivot will assume midnight if you don't specify a time in your date column). But I'm simplifying since that is not needed for this one scenario.

Next make your two base calculated measures

  • Number Active:=Sum(Active[number_active])
  • Total Revenue:=Sum(Purchase[revenue])

I'm not sure what your number active means, so I'm assuming it is just a sum. You can adjust this calc if this is not the case.

Then make your final calculation: Revenue Per Active User:= [Number Active]/[Total Revenue]

Then you should be able to pull month and year into a pivot table along with this new calculated measure and your original measures (number active and revenue).

Upvotes: 1

Related Questions