Active Member
June 24, 2016
Hi, hope someone can help. I've used Excel for years, but only very recently got into Power Query, PowerPivot and Power View.
I'm working with a system DB that holds logged event records and these are held in a single table. Actions taken on those records are stored in a separate table. Each action contains a shared reference back to the main record table, so the actions can be used as a lookup. To measure the time a record has been open but also in different states, based on it's respective actions, I need to query the records table multiple times to get when the record was moved into a state and again moved out, and every time it happened. The process of moving into and moving out of a state uses a specific action type for each, and the actions are dated.
What I have done is create extracts via Power Query, so only working with the records I need and the respective actions. What I think I need to do is use PowerPivot to use the actions query table as a lookup and consolidate the action data into a single calculation. What I do not have clue about is any DAX function that can carry out the consolidation for me.
i.e. Record A has been put into state x 3 times, and the action table has 3 actions for moving from state x to state z, and 3 for moving out of state z and into state x. I need to work out how much time Record A has spent in each of the 2 states across its lifecycle.
I would love to share some sample data but security concerns will not allow me to do so. I can answer non-specific questions about the data if necessary.
Anyone have any suggestions for me?
Active Member
June 24, 2016
OK, to update, I've worked out how to consolidate the dates in the second table, by modifying some of the values to a negative (based on specific rules) so the net sum of these values gives the actual time expired. What I cannot workout is how to get a sum lookup to work, so that when I go back tot eh master table I can sum all of the adjusted values for each master record. I've tried using a SUM calculation in the master Power Pivot table but it simply sums the whole column, not the specific values applicable to each master record. Anyone got any ideas at all?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Active Member
June 24, 2016
Catalin,
The attached is pretty much the best I can do without breaching any confidentiality that applies. The real data and system are sensitive, so I cannot extract a 'real' sample, but I think the attached gives you the gist of the issue.
I need to calculate the sum of the time expired within the actions. I've sued the adjusted values to get to a positive value for on hold and negative value for off hold, with the aim of simply summarising the actions for each ticket. What I need in column E is the DAX (or normal formula for that matter) that looks up the ticket ID and summarises all action entries for that ticket.
I should point out that on the actions table, they are recorded in date order, though I can of course apply a custom sort in the Power Query if sort order is a factor.
Si
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
1 Guest(s)