manekankit
Board Regular
- Joined
- Feb 1, 2019
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
Context: Preparation of income statement through power pivot.
Data Source: period wise details of income and expense loaded through external excel file to power query and power pivot model.
Left side table:
1. This left side table is loaded to power query and power pivot data model through external excel
2. It has year to date cumulative (Jan to Nov, Janto Oct and so on) numbers of income, expenses and tax for various months
Right side table:
3. This right side table is the output required automatically through power pivot table (currently it is manually prepared for taking screenshot)
4. Rows highlighted in grey are calculated values (e.g. Profit before tax = total income - total expenses)
5. Columns highlighted in orange are calculated value (e.g. 'Monthly Nov'22' column = 'YTD Nov22' minus 'YTD Oct'22' and so on)
Questions:
1. How to add calculated rows (like 'Total Income', 'Profit before tax' and 'Profit after tax' in the pivot table?
2. How to add calculated columns (like 'Monthly Nov-22', 'Oct-22' and 'Monthly Sep-22')
3. Currently data source contains four months (Aug22 to Nov22), however it will also increase in future (i.e. in next month it will be Aug22 to Dec22 and accordingly in right side table, one additional column for monthly Dec22 will be added).
Requesting community members to guide how to generate required output table through power pivot or any other alternative.
Image:
not able to attach images here due to some technical error,
you may refer images on below link of the Microsoft forum
Link to image
Data Source: period wise details of income and expense loaded through external excel file to power query and power pivot model.
Left side table:
1. This left side table is loaded to power query and power pivot data model through external excel
2. It has year to date cumulative (Jan to Nov, Janto Oct and so on) numbers of income, expenses and tax for various months
Right side table:
3. This right side table is the output required automatically through power pivot table (currently it is manually prepared for taking screenshot)
4. Rows highlighted in grey are calculated values (e.g. Profit before tax = total income - total expenses)
5. Columns highlighted in orange are calculated value (e.g. 'Monthly Nov'22' column = 'YTD Nov22' minus 'YTD Oct'22' and so on)
Questions:
1. How to add calculated rows (like 'Total Income', 'Profit before tax' and 'Profit after tax' in the pivot table?
2. How to add calculated columns (like 'Monthly Nov-22', 'Oct-22' and 'Monthly Sep-22')
3. Currently data source contains four months (Aug22 to Nov22), however it will also increase in future (i.e. in next month it will be Aug22 to Dec22 and accordingly in right side table, one additional column for monthly Dec22 will be added).
Requesting community members to guide how to generate required output table through power pivot or any other alternative.
Image:
not able to attach images here due to some technical error,
you may refer images on below link of the Microsoft forum
Link to image
