I'm wondering what the best way to approach this is....
I have an sql data table behind this pivot table that includes ppm_id, Estimate_Type and the individual row values for Total_Estimate. I used Power Query to add a conditional field to get the Est_Weight. The purpose of the pivot table was to get all the data rows for a given Estimate_Type.
So,
Initial ROM = 1,
Post-Rqmts=2
Post-Design =3
Blank =0
My thinking was that I could then look for MAX of Est_Weight to leave me 1 row per ppm_id. So now my challenge is to see if I can filter this pivot table to be just 1 row per ppm-id with the current Estimate_Type and Total_Estimate for that row.
If I need to do this with a table vs a pivot table would I be better just creating the table to begin with or use the pivot table to create the table.....assuming I can't do this with a pivot table of a pivot table....if that makes sense.
The end goal is to be able to use the ppm_id to get the do an Index/Match to add the Total Estimate to another user pivot table or excel table, so this is a supporting table to get data in the right format if that makes a difference.
Thx,
Don
I have an sql data table behind this pivot table that includes ppm_id, Estimate_Type and the individual row values for Total_Estimate. I used Power Query to add a conditional field to get the Est_Weight. The purpose of the pivot table was to get all the data rows for a given Estimate_Type.
So,
Initial ROM = 1,
Post-Rqmts=2
Post-Design =3
Blank =0
My thinking was that I could then look for MAX of Est_Weight to leave me 1 row per ppm_id. So now my challenge is to see if I can filter this pivot table to be just 1 row per ppm-id with the current Estimate_Type and Total_Estimate for that row.
PPM_MBR_SQL_v2_20200812.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
3 | ppm_id | Estimate_Type | Est_Weight | Sum of Total_Estimate | ||
4 | 46088 | Post-Design | 3 | 1798 | ||
5 | Post-Rqmts | 2 | 1798 | |||
6 | 46241 | Post-Rqmts | 2 | 798 | ||
7 | 46650 | Initial ROM | 1 | 500 | ||
8 | 46678 | Initial ROM | 1 | 51 | ||
9 | 46719 | Initial ROM | 1 | 94 | ||
10 | 46780 | Post-Rqmts | 2 | 191 | ||
11 | 46781 | Post-Rqmts | 2 | 1720 | ||
12 | 46856 | Initial ROM | 1 | 85 | ||
13 | 46859 | Post-Design | 3 | 201 | ||
14 | 46971 | Post-Design | 3 | 96 | ||
15 | 47165 | Initial ROM | 1 | 268 | ||
16 | 47168 | Initial ROM | 1 | 416 | ||
17 | 47170 | 0 | 40 | |||
18 | Initial ROM | 1 | 757 | |||
19 | 47171 | Initial ROM | 1 | 895 | ||
20 | 47199 | Initial ROM | 1 | 2000 | ||
21 | 47200 | Initial ROM | 1 | 385 | ||
ptEstimates |
If I need to do this with a table vs a pivot table would I be better just creating the table to begin with or use the pivot table to create the table.....assuming I can't do this with a pivot table of a pivot table....if that makes sense.
The end goal is to be able to use the ppm_id to get the do an Index/Match to add the Total Estimate to another user pivot table or excel table, so this is a supporting table to get data in the right format if that makes a difference.
Thx,
Don