•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Column Subtotals in Power Pivot
Our take
In Power Pivot, organizing your table to display Actual and Budget numbers along with their subtotals can enhance clarity and facilitate analysis. You can achieve a structured layout by using the existing fields for Actual and Budget, but positioning subtotals for each section can be tricky. While you currently see Grand totals extending to the right, consider leveraging the 'Subtotal' functionality within your Pivot Table settings. This approach allows for cleaner presentation without the need for creating multiple measures, streamlining your data management process.
I have a basic Power Pivot table fed from a single data source. There is a field for Actual and Budget and a single field for values.
I am trying to lay my table out so I have a set of Actual numbers with a column field and subtotal, then a set of Budget numbers with the same column field and subtotal. Rinse and repeat for the variance column.
The issue is I can only add Grand totals out to the right. I cant get that total /subtotal column positioned with each set of numbers. Short of making a measure for each column, is there a better way? I feel like I'm overlooking something simple.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Separating Monthly Results in Data for Same Item?Hey all, I’ve got a dataset that I’m having some trouble separating for calculating properly on a pivot table. It is a list of items in Column A, which all belong to a group in Column B, and then C D and E are results for Q1 so Jan, Feb, and March. the problem is that we have a budget figure, a forecast figure, two new forecast figures and the actual figure for each item, that are stacked in the column. so for the first item there are five rows of data that I would like to compare for each month. I just can’t figure out how to do this in a pivot table because the forecast/new forecast/budget/actuals aren’t column headers. I tried adding new columns that totaled up the rows for each category of forecast/actual etc but the results were far more than the real actuals so something failed there. Thanks! submitted by /u/Woodit [link] [comments]
- Trouble using pivot tables to calculate field and subfield values.I am having trouble getting a clean summary of our monthly stats for a surgical practice I work for. I am using a pivot table connected to the surgical case spreadsheet. In the spreadsheet, each row represents an individual case. I am struggling to get these values in the same field: 1) Monthly total of days the practice performed surgery 2) Subfields showing Monthly total of days each doctor performed surgery The issue is that I cannot figure out how to calculate both of these in the same field. "Sum of Days Worked" returns the correct monthly total, but the wrong total of days for that individual doctor. This is because a "1" gets placed in this column next to the first surgery of the day. So, that value only gets assigned to the doctor who performed the first surgery of the day. "Sum of Dr Day" returns a "1" on the first surgery each doctor performed that day. This calculates the correct total of days each doctor performed surgery, but the monthly total is off, because if there are more than one scheduled per day, it counts the day twice. I understand I can achieve the desired totals with regular formulas, but we are constantly having new doctors join and leave our practice, so I prefer pivot tables because I don't have to continuously adjust formulas. Google tells me "Add this Data to the Data Model" is helpful for this problem, but we only use Macbooks at work. Any help is appreciated- this is driving me crazy. submitted by /u/anarchybats [link] [comments]
- Totaling responses from a field?This is a pretty beginner question I think but my brain is fried today. I have a spreadsheet with 3 columns. The first two I don't need at all. The last one is the filled with answers to "How did you find us" on our checkout page. I want to make a graph or pie chart that totals all the answers and shows the most common responses by percentages or count. The column's data has several pre-filled answers they can select, but they can also write their own so there's tons of custom responses. I don't have a way to reformat it so each response is in it's own column which I think is what I need to do to properly display it? Any help would be great! submitted by /u/fullofpaint [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#financial modeling with spreadsheets#rows.com#big data management in spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions#Power Pivot#subtotals#Grand totals#Actual numbers