Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have below scenario.
Have sales Table with different part numbers. I want to create below table (Required format) using Pivot table.
Is it possbile?
Note: I do not want to create combination of parts (P1+P2, P3+P4, R3+R5, R1+R4) as dimensions.
Sales Details |
| Required Format | |||||||
Company | Part | Month | Value |
| Company | Part | Q1 | Q2 | Q3 |
a | P1 | Q1 | 80 |
| a | P1+P2 | 122 | 99 | 69 |
a | P2 | Q1 | 42 |
|
| P3+P4 | 120 | 152 | 161 |
a | P3 | Q1 | 82 |
|
| P5 | 23 | 91 | 29 |
a | P4 | Q1 | 38 |
|
| Total | 265 | 342 | 259 |
a | P5 | Q1 | 23 |
| b | R3+R5 | 92 | 136 | 87 |
a | P1 | Q2 | 40 |
|
| R1+R4 | 159 | 137 | 139 |
a | P2 | Q2 | 59 |
|
| R2 | 97 | 66 | 84 |
a | P3 | Q2 | 71 |
|
| Total | 348 | 339 | 310 |
a | P4 | Q2 | 81 |
|
|
|
|
|
|
a | P5 | Q2 | 91 |
|
|
|
|
|
|
a | P1 | Q3 | 41 |
|
|
|
|
|
|
a | P2 | Q3 | 28 |
|
|
|
|
|
|
a | P3 | Q3 | 95 |
|
|
|
|
|
|
a | P4 | Q3 | 66 |
|
|
|
|
|
|
a | P5 | Q3 | 29 |
|
|
|
|
|
|
b | R1 | Q1 | 96 |
|
|
|
|
|
|
b | R2 | Q1 | 97 |
|
|
|
|
|
|
b | R3 | Q1 | 69 |
|
|
|
|
|
|
b | R4 | Q1 | 63 |
|
|
|
|
|
|
b | R5 | Q1 | 23 |
|
|
|
|
|
|
b | R1 | Q2 | 84 |
|
|
|
|
|
|
b | R2 | Q2 | 66 |
|
|
|
|
|
|
b | R3 | Q2 | 51 |
|
|
|
|
|
|
b | R4 | Q2 | 53 |
|
|
|
|
|
|
b | R5 | Q2 | 85 |
|
|
|
|
|
|
b | R1 | Q3 | 81 |
|
|
|
|
|
|
b | R2 | Q3 | 84 |
|
|
|
|
|
|
b | R3 | Q3 | 49 |
|
|
|
|
|
|
b | R4 | Q3 | 58 |
|
|
|
|
|
|
b | R5 | Q3 | 38 |
|
|
|
|
|
|
Thanks
What do you mean "I do not want to create combination of parts (P1+P2, P3+P4, R3+R5, R1+R4) as dimensions"
You want to have P1+P2, P3+P4, R3+R5, R1+R4 as dimension values, but then again you don't?
Here the trick is to develop expressions and pull them as Rows (Dimensions) in pivot.
if we do so expression P1+P2 will be visible seen for both the companies.
Question is how to hide P1+P2 for company B (as it is not applicable for company B)
How to hide R3+R5 for comapny A?
Please refer attachment.