•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Showing a row where no value exists
Our take
When working with pivot tables, displaying rows for categories without sales data can be challenging, especially when certain categories, like Consulting for New Zealand, are missing from the source data. To address this, consider creating a custom table with your product categories as row headings: Phone, Consulting, and Internet. You can then utilize XLOOKUP or SUMIF functions to accurately retrieve sales amounts from your pivot tables. This approach ensures that all categories are represented, providing a clearer picture of your sales landscape.
I have a list of 5 countries we sell products to. And we split it into 3 product categories: internet, consulting services, phone.
I have used power query to and loaded the connection into the data model, but not as a table within a worksheet.
These are my total sales amount pivot tables:
Filter: USA
phone | 500 mill
Consulting | 20 mill
Internet | 30 mill
Filter: New Zealand
Phone | 5 mill
Internet | 20 mill
Problem: I need New Zealand to show Consulting and a sales amount of 0.
There is no “consulting” row in the source data itself for New Zealand so “show zero values” in pivot table settings doesn’t work
I figure I’ll need to create my own tables listing: Phone, Consulting and Internet as row headings. But how should I get my sales sums?
I’m guessing doing xlookups using my pivot table is the data source is not best practice
Do I scrap my pivot tables and load the power query as a table for sumif?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]
- pivot tables - exist something like filter by column?Good morning community. I don’t usually use pivot tables much, because I tend to prefer building my reports with filters and formulas, but my boss loves them. The problem is that many times he doesn’t even know exactly what he really wants. Here’s the issue. I have a table where data is divided into categories, for example: Column A – Primary Categories Column B – Secondary Categories Column C – Tertiary Categories From Column D onward… prices, with each column representing a different month. My boss wants a pivot table where he can filter by categories (I already have that done), but also be able to filter somehow by just one month, or several, or all of them, since he then wants to use that in a chart (this part is also already done). AND HE ONLY WANTS TO SEE THE CHART AND CONTROL THE DATA FROM THERE—in other words, he doesn’t want to have to go into the pivot table to edit it by adding or removing columns. So the question is… how can I (if it’s even possible directly from the pivot table) quickly change the columns I’m displaying with a button? That is, without having to manually edit the pivot table. So far, the solution I found was to create a “column” using formulas where I bring in the data with “HLOOKUP” and change the filter with a dropdown list linked to a macro that refreshes the pivot table every time its value changes. But I’d like to know if pivot tables have a more direct way to solve this. Thank you very much. submitted by /u/Potential-Aside-1712 [link] [comments]
- Sum unique values in Pivot Table without Data ModelI have a spreadsheet that tracks the surgical cases our practice sees. In this spreadsheet, each case is its own row. Column A is the date. Column B is the primary doctor assigned to the case. Column C lists which doctors are present at the practice that day (ex: "Dr.X" or ""Dr.Y" or "Dr.X/Dr.Y"). The first case of the day gets a "1" in Column D. I am trying to use pivot tables to create a summary of some metrics for the surgeries. Part of the stats we look at are how many surgeries a doctor performed when there are two working that day vs just one. The issue I'm running into is when I try to run statistics by the month, filtered by Column B and Column C, I cannot get an accurate count of the number of days a doctor worked that month. If I want to run stats for Dr.X on days both doctors worked, Column D only gets added if Dr.X was assigned to the first case of that day. I know that you can add unique values by using the Data Model feature, but this isn't available on Mac. Is there any other way I can calculate this? submitted by /u/anarchybats [link] [comments]
- Column Subtotals in Power PivotI 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. submitted by /u/NotBatman81 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#row zero#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#natural language processing for spreadsheets#rows.com#Excel compatibility#Excel alternatives