Adding measures in a data model at a summary level - how ?
I'm probably not going to describe this very well - but here goes 🙂 I am new to data modeling and am trying to convert old files / pivot tables to new table design/data model structures and at the same time develop some new reports for key stakeholders. The attached file has a table (I know its not optimal in terms of structure - but way better than it was). This table (called Hours_New) collects employee time daily. The time is regular, ot or unpaid. It is also chargeable or non-chargeable (as determined by the column in the table titled SC & CD Combo. There are productivity targets (50% for labourers, 75% for other trades). What I want to be able to do is report the productivity % at various levels (Foreman, Trade, Time Period etc etc etc). When I build pivot tables to try and do this I'm not able to get the results presented as I want them - I've taken a couple of stabs at it, but the results are way too busy & I want to be able to highlight when targets are being missed (on both upper and lower ends). I'm sure its not all that tough to do but I'm spinning my wheels - grrr. The attached file has multiple tabs and I've trimmed the dataset down - its actually close to 500,000 rows and removed most of the other pivot tables not related to what I'm trying to do - any help would be most appreciated !!
A new Excel Think Tank
After nearly 30 years of using Excel commercially, I am now coming to retirement. But before I finally hang up my Excel boots, I have setup a small Excel think tank. The idea being people can send me their issues and I will work with you to build your permanent solution in Excel. I have created a number of solutions from Email Validator, Automatic dashboard creators, Fraud analysis, Auto resume makes, Music Syns (All in Excel), so if give it try.What is this Madness? Weird Average and Sum results
What is this devil math? What am I missing? The darker green value and blue value should match. I checked for truncated decimal values, even rebuilt the sample to ensure nothing was weird about the original cells. This is not an insignificant difference for what I'm doing, and I don't understand why these aren't lining up. I'm sorry if I'm missing something simple, but please, help!
Managing Blank Cells in multiple linear regression analysis.
Hello, I am performing a multiple linear regression analysis using the Analysis Toolpak in Excel 365. I have complete y-data, but the x variables are missing some data points throughout a large data set. I know I can fill in the data with the mean or potentially use a regression analysis to predict data to fill in the blanks; however, I would prefer to ignore the blank data if possible. Is there a way to do this inside the data toolpak?
Excel 365 Sheet Views are not working
I have 7 different Sheet views in a shared document, they were working fine until about a month ago. Since then I have tried everything, I deleted them, created them again and they don't work. When I create them they look fine but once my collaborators enter to work on the spreadsheet all the views start showing the default view. The sheet has the black border and the eye in the tab. How can I solve thisExcel Pivot Table shows all records but one row is displaying blanks for some columns
Hello, My Excel pivot table returns all rows that should be returned according to filters applied directly to column filters in the pivot table itself, or via some Slicers that I've created off of this pivot table. For 2 particular Business Units, there is one row (again, that is properly returned as a row in the filtered pivot table - i.e., no 'missing' records) that shows values in the first 4 columns of the pivot table, but does not display the values in the subsequent 9 columns, despite the fact that the data is fully populated in the Source data. The one source field I've added to the 'Values' section of the pivot table (Report Count) does display values as expected. Troubleshooting tried so far: I've ensured that my Named Range data source is properly capturing the entire source dataset - i.e., no dataset rows or columns left off I've refreshed my pivot table numerous times after changing filters directly in the pivot table itself, as well as changing the filters in Slicers built off of that pivot table. In the Pivot Table Options, on the 'Data' tab: the 'Number of items to retain per field' is set to 'None'. And the 'Save source data with file' check box is not checked off. I've deleted/re-added the source data for the problematic, 'invisible' pivot table columns (not showing the data as expected) and refreshed the pivot table, but that did not correct the issue. I inserted a blank row adjacent to 1 of the 2 problematic records in the source data and copied the data from the problematic record into it, and deleted the original record and refreshed my pivot table, which also did not remediate the problem. I've also built another pivot table in the same workbook on a different Excel worksheet (using the same Named Range data source) and added all the same fields for one of the problematic records and they are all displaying normally. The only error message of any kind that is generated is when I go to one of the problematic records in the pivot table itself, select one of the cells in that row that is displaying a blank and then try to click on the Formula bar - the error message is, 'We can't change this part of the pivot table'. But, I believe this is a normal message if Excel senses that you're trying to update data in a pivot table. There are no error other messages or difficulty refreshing to report. Other relevant points: All told, I have 239 total rows in my source data, and all rows appear normally in the pivot table with the exception of these 2 problem records. There have been no other performance/display issues of concern outside of this one. This Excel workbook is mainly utilized on SharePoint by multiple Users, but for more complex updates/troubleshooting, I am easily able to bring it down to work in Excel Desktop as needed - and this problematic display with these 2 records is present regardless of whether I'm working with the file on SharePoint or with Desktop Excel. I can't think of what else to try at the moment - I've never seen anything like this in my 25+ years of working with Excel. I hope I'm missing something simple - any troubleshooting suggestions are greatly appreciated!Excel formulas starting to include current sheet name
Hi I recently noticed that formulas in excel starting to automatically include the "current" sheet name after leaving and coming back to the sheet (example below). I don't remember this used to be the case but I could be wrong. Does anyone have a similar experience or can confirm if this is correct? Is there a setting somewhere I can turn this on and off? Example: I have 2 tables (1 per sheet). After clicking on Sheet2 to select the cell I want to sum, coming back to Sheet1 and referencing any cells will include "Sheet1!" in my formula. I believe previously the formula omits "Sheet1!". Both ways are useful in different scenarios but wondering if I have missed a trick to "turn on and off" how it behaves. Thanks!
Complex formulae with data model
Over the years, I have developed a significant excel data sheet which has gotten slower and slower with time. I have discovered the idea of excel's data model this week and have learnt how to import the data I need from its source CSV to the data model and I now have a power pivot which looks like what I originally had in excel. What I would like to do next, is to replicate some of the formulae I had in excel to calculate if something should be counted or not. I would not want the red one to be counted as this row would have been counted in the previous month. In excel I was using the following formula- =IF(COUNTIF(B5:M4,">0"),SUM(Patient!B5:M5*0),Patient!N5) is there a way of achieving this with the data model or would I have to hardcode this into another sheet? I was trying to avoid doing this to minimise the number of formulae... Many thanks for your help...
