Pivot Table / Powerpivot Calculated Column and Drill Down

[fliplid1992]

Hi Everyone,

This is my first time posting, so please excuse any accidental forum faux pas. I am trying to create a pivot table that summarizes how many parts remaining at each process sequence. Ideally, when it's complete it'll also be able to drill down on each sequence to see the number of scrap for each scrap code, the number of days those parts have been at that sequence, and the sales dollar value of that group of parts.

Just to start, I've been working on calculating the number of pieces remaining at each sequence. That part of the data is stored in 2 tables, LOAD and LABOR. The LOAD table has the number of pieces ordered with a row for each sequence of each work order. Since I couldn't combine 2 many-to-many relationships into 1 one-to-many, I created a column called "Key_ID" that concatenates the work order number and process sequence number joined by a "-". The LABOR table stores the date, number or pieces completed, number of pieces scrapped, and the scrap code (if applicable, otherwise "0"). The LABOR table has a row for each time an operator logged on to the job ("many" side of the one-to-many).

I'm trying to make the number of pieces remaining column calculate in the pivot table ([# pcs ordered] - [# pcs complete] - [# pcs scrapped]). However, the "calculated field" option is grayed out. I was almost able to do what I wanted in Powerpivot by creating a calculated field in the LOAD table with the DAX expression CALCULATE(), but I think I'm losing my relationship to the number of pieces for each scrap code in the LABOR table.

Furthermore, I'm lost when it comes to drilling down on each of the "pieces remaining" values to find the scrap details, number of days the work has sat stagnant, and the sales dollar value (I'm still working on relating the sales dollar table which is a separate, 3rd table in the relationship).

Please see below for screenshots of my data. All help is appreciated! Especially since I have been confused for weeks with this problem.

 

[Gordonik]

First of all. I am not sure your screenshots are visible. You can do this with a measure but make sure your relation is working. Without screen is difficult but it should be something like this:

REMAINING PIECES:=CALCULATE(SUM(LOAD[# pcs ordered])-SUM(LABOUR[# pcs complete])-SUM(LABOUR[# pcs scrapped]);LOAD;LABOUR)

 

[fliplid1992]

First of all. I am not sure your screenshots are visible. You can do this with a measure but make sure your relation is working. Without screen is difficult but it should be something like this:

REMAINING PIECES:=CALCULATE(SUM(LOAD[# pcs ordered])-SUM(LABOUR[# pcs complete])-SUM(LABOUR[# pcs scrapped]);LOAD;LABOUR)

Thanks Gordonik,

I was able to use that formula in a calculated column on the LOAD table in the Powerpivot window, but not in the pivot table itself. Is it possible to calculate a value for each row (e.g. calculated column) in the actual pivot table?

Also, how would I go about "drilling down" for those other details about each group of pieces remaining?

P.S. I will try to re-upload my screenshots, but idk if this forum will let me since I'm a new member.
P.P.S. Instead of re-uploading, I think it will be more reliable if I just post the direct link to them on my Google Drive.


Close to how I want the pivot table to look like, but with 2 columns instead of just 1: https://drive.google.com/open?id=0BxMonSmzLTAMazhLRC1qZHY2UU0

LABOR Table: https://drive.google.com/open?id=0BxMonSmzLTAMODJvSXRGZ0txc0k

LOAD Table: https://drive.google.com/open?id=0BxMonSmzLTAMNFlWNy1SaEdVd00

My first attempt with all 3 columns needed for the calculation (but I only want the calculated column in the end / the option in Calculations under Fields, Items, & Sets for "Calculated Field" and/or "Calculated Item" is grayed out): https://drive.google.com/open?id=0BxMonSmzLTAMVzI0REw1RER1X28

 

[Gordonik]

The screens shows you have not installed PowerPivot ad-in or it is not active. There are few tabs on a ribbon as "View", "Developer" you shoud have "PowerPivot" here too.
What you have done is just created a "table" from a range of data. Next step after you install the add-in is to teach how to import this table to "data model". Then you can start to use calculations.
Calculated Field/Item are used in classic Pivots. In PowerPivot tables it is replaced by "Measures" and "Calculated Columns". Calculated Field/Item is always grayed out when you build a pivot based on a "table" or "data model".

You should start from theory:
- What is the "data model" in PowerPivot
- what are measures and calculated columns
- how to do relations in PP