Hello!
I am attempting to follow the advice found in a blog by the fine people at PowerPivotPro (https://powerpivotpro.com/2018/01/star-schema-switch-drill-income-statement-design/) and have most things in place, with exception of the header sort sequence.
Below is the header table that is part of the powerpivot data model
<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
</tbody>Header
The resulting pivot table shows the header line in alphabetic order. I would like to maintain hte data source order as shown above.
<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]18[/TD]
</tbody>
When I select inside the pivot table - 2 trails to follow
1) find an option for "pivot table options", and within "Layout and Format", under field list, the "sort in data source order" is toggled.
2) Select the first row "Crop Revenues", right click and follow hte "sort" menu. There is a "more options", which then allows to toggle "Data source order"
I have downloaded, and studied the file provided by PP3, and I am not finding any differences in pivot table options. The only difference is the source of data - I redid the COA/report layout to fit my needs, and using a table import into the data model, versus PP3 using a query.
I greatly appreciate the forum's expertize and willingness to share!
daryl
I am attempting to follow the advice found in a blog by the fine people at PowerPivotPro (https://powerpivotpro.com/2018/01/star-schema-switch-drill-income-statement-design/) and have most things in place, with exception of the header sort sequence.
Below is the header table that is part of the powerpivot data model
A | B | C | D | E | |
---|---|---|---|---|---|
HeaderID | Header | Summary | Show Detail | Var Display | |
CROP REVENUES | |||||
NON CROP REVENUES | |||||
TOTAL REVENUE | |||||
DIRECT COSTS | |||||
INDIRECT COSTS | |||||
INVTY ADJUSTMENTS | |||||
PRODUCTION COSTS | |||||
PRODUCTION MARGIN | |||||
G&A | |||||
OPERATING MARGIN | |||||
OTHER INCOME | |||||
FINANCING | |||||
TAXES | |||||
NET INCOME |
<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
</tbody>
The resulting pivot table shows the header line in alphabetic order. I would like to maintain hte data source order as shown above.
B | |
---|---|
Row Labels | |
CROP REVENUES | |
DIRECT COSTS | |
FINANCING | |
G&A | |
INDIRECT COSTS | |
INVTY ADJUSTMENTS | |
NET INCOME | |
NON CROP REVENUES | |
OPERATING MARGIN | |
OTHER INCOME | |
PRODUCTION COSTS | |
PRODUCTION MARGIN | |
TAXES | |
TOTAL REVENUE | |
(blank) |
<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]18[/TD]
</tbody>
Pivot
When I select inside the pivot table - 2 trails to follow
1) find an option for "pivot table options", and within "Layout and Format", under field list, the "sort in data source order" is toggled.
2) Select the first row "Crop Revenues", right click and follow hte "sort" menu. There is a "more options", which then allows to toggle "Data source order"
I have downloaded, and studied the file provided by PP3, and I am not finding any differences in pivot table options. The only difference is the source of data - I redid the COA/report layout to fit my needs, and using a table import into the data model, versus PP3 using a query.
I greatly appreciate the forum's expertize and willingness to share!
daryl