PowerPivot "Relationship Between tables may be needed"

[dheeraj1688]

What actually i'm facing .
- there is two different sheets.
1. Main Log sheet- where tickets(primary key i assume) with estimated hours are logged.
2.Timesheet- where every person work and log there actual hour spend on ticket.

What i did insert both tables into data model. created relationship between both tables as.
Timesheet- Ticketno (foreign) and main sheet- Ticketno(primary).

now i want to create table
Different ticket no. -- First Column (time sheet Total hours on ticket) and second column (Estimated hours )


Attaching File for reference.. Please Help to solve this problem..

THANKS IN ADVANCE .

 

[Somendra Misra]

Hi Dheeraj,

Do you want something like this?



Regards,

 

[dheeraj1688]

Hi Dheeraj,

Do you want something like this?

View attachment 14066

Regards,

hi somendra ...thank you fr reply .. yes i want this type of solution can tell me how to do this..

 

[Somendra Misra]

@dheeraj1688

Well I had Excel 2010 and you have Excel 2013. So I think my data model will not works on yours, so try these steps.

1. Import both the table in PowerPivot.
2. Create relationship as shown below:


3. Than Create PT with below mentioned details.



Just try this.

Regards,

 

[dheeraj1688]

@dheeraj1688

Well I had Excel 2010 and you have Excel 2013. So I think my data model will not works on yours, so try these steps.

1. Import both the table in PowerPivot.
2. Create relationship as shown below:
View attachment 14072

3. Than Create PT with below mentioned details.

View attachment 14073

Just try this.

Regards,

hi , I'm getting the result .. But i want ticket no. from timesheet (Reg Table)..
As main sheet is update daily, ticket added for future. So unable to compare between current month ticket worked and actual hr of IMP Table.

I'm Attaching pic for reference as u can see gap in pivot table. I'm unable to compare actual hr of time sheet .

 

[Somendra Misra]

I don't think dheeraj. I think Your master table for Ticket No. is IMP and daily data entry is in REG table. So like that it has to be like what I said.

You can keep ticket no. from IMP table.

May be you take consultation of somebody who is expert on Power Pivot.

Regards,

 

[dheeraj1688]

Hi Somendra ,
Thank you For reply.
Defining a Case.
what scenario we following.
My team pick tickets from IMP table(main sheet), work on it daily and log there hours spend on tickets in timesheet(REG table). Now after a month there are many tickets left or carry forward to next month. I want to check only how many tickets they worked on last month and what were the estimation they provided in IMP(Main Sheet).
Now what solution you provided no doubt it's correct but it bring all tickets in the pivot table and difficult to compare as total is of all tickets not the tickets they actually worked on last month.

 

[Somendra Misra]

Does REG & IMP Tables updated monthly for the same ticket no.?
OR
REG table is updated daily and IMP Table only when the ticket is created for the first time?

Regards,

 

[dheeraj1688]

REG Table is Actually a Timesheet where developers log there daily (9 working hour) spend on single/different tickts .
IMP table is update only when new ticket come or when developer think estimation provided for ticket should be increase /decrease because of the new requirement come in between.

Case 1
Suppose Dev working on ticket no. 3456 for a month and log 5 hr daily in his timesheet(REG Table) he spending on 3456.
Dev provide estimation of 100 hr for 3456 in IMP table.
In Month end i will pick 3456 (or All tickets) from REG table(timesheet) sum up and compare with imp table ,what actual estimation he provided against his tickets.

Case 2
There are chances when multiple persons work on single tickets.

Sorry for writing too much . But i want to make sure you get a clear picture what problem im facing and help me out.
thank u..

 

[dheeraj1688]

MAy be this will help also..
http://www.powerpivotpro.com/2013/0...i-already-have-a-relationship-whats-going-on/

 

[Somendra Misra]

Hi Dheeraj,

See this file, I had made it on 2010, if it did not work on your 2013, than try on some system with 2010 and if everything is ok than replicate the same on 2013.

Regards,

 

[dheeraj1688]

Hi, Somendra ..
THANK YOU for reply and that's perfectly working.