model

Our Internal Risk framework covers Excel models. At the moment, users classify the level of risk associated with each model themselves, as high, medium, low depending on the complexity of the models (e.g. if the owner got knocked down by a bus would someone else be able to maintain it) combined...

I have a simple DAX measure in my Excel 2016 data model: [Who Am I Domain]:=USERNAME() When I enter this as a measure in the data model window and use autofill the end parentheses appear. However, after the measure calculates the parentheses disappear (which they don't do for...

Hi, I'm doing my first baby steps in power query. I tried to create query from excel workbook through the 'Get and and transform' and it took ages, but when I tried to do the same through the "Data model' UI it took just seconds... Why is that? What is the difference between the two methods...

Hi All, First time posting, been a long-time reader. I have a question that I'm sure would have been looked in to before but cant seem to get a fix via solver or any of my own VBA work. I have a model with 96 different possible outcomes dependent on the variables in 4 different drop down...

Hi All, I have a sheet from a manufacturer that tells me which models get which rewards. It looks like this... <tbody> MODELS REWARD X1245, J4541, Y4154 $15 A1015, N1522 $20 </tbody> In order for me to use v-lookup (as I have thousands of line items), How to I transpose this so that...

<colgroup><col span="7"></colgroup><tbody> Model spec colour model 1 a b c white black grey model 2 a d e green white blue model 3 c f g yellow red black </tbody> Is that anyway to create the 2nd(spec) and 3rd(colour) drop down list base on the selection of 1st drop downlist(Model)?

Hi All! I have a spreadsheet with more than 15K lines of data. This is the product of combining about 10 different versions of the same spreadsheet (there was no real version control before I started working on it). The data are grouped by content in cells in column A (VBA code to insert blank...

Hi, I'm trying to come up with a dynamic suming range within sumifs formula. I'm trying to sum between today and today +12 with several conditions. How can I get to the below result with a SUMIFS? What I'm struggling with is to come up with a dynamic summing range. Most probably with OFFSET...

I have two tables (Table1 and Table2), each on its own worksheet, that have been added into the data model. I created a third table (Table3) by appending Table 2 to Table 1. I chose "Only Create a Connection" and added the third table directly to the data model. Since the third table doesn't...

How do I reference a table ("QA_Full") that exists solely in the data model and not on any worksheet? I know how to do it when the table is on a sheet (i.e., Set MyTable = Worksheets("Sheet1").ListObjects("QA_Full")), but how do I accomplish the same thing when the table only exists in the data...

Hello everyone, I wanted to know if there was a formula or macro that I could use which would look up a string of text in column B:B and if found in column D:D return the value which correlates to its row, but the value i want is in column A. Its hard to explain but i am attaching an image...

Hi, The objective is to come up with the formula in F4 that give you the number of months in takes to deplete the current inventory (F3) based on your current sales forecast (G2:02) (I put the expected results the formula should obtain) I included the expected results in row 4. Note that the...

Hi folks, My colleague sent over a workbook by in which a powerpivot model was created, I was tasked with adding a few data fields and refreshing for latest months data (so we need to appended rows/columns with new data when it comes along)... As title suggests, power pivot model is not...

Hello All, I'm currently looking for a way to calculate months on hand inventory. Looking around this forum I could not really find a simple way to get rid of my problem. My dataset is as follows; <tbody> Model Region Configuration Measure Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19...

Hello All, I need your help. I'm working on a file where I need to search horizontally and vertically with multiple conditions. What i did try before coming asking for help here? a) I tried an array formula which worked. However my file contains more than 50,000 rows. This makes the file way...

I have some data in the data model, and when I go into the measures to create a new one, the tables/columns etc are not pulling up as choices to use hen start typing them. I thought well maybe my query was broken so I physically copied the spreadsheet into the workbook and tried adding to the...

Hi all, I have data on 3 different tabs. 1 for stock on hand, 1 for sales, and i need to convert them to the the way i have it on the "Data for Pivot" tab. The data i have cannot be changed as this is the format i get it in. I basically want to combine SOH and sales by store, but then be able...

Hi, I have a forecast model generating forecasts for financial account line items in departments. I wish to display my output in a pivot table with the row order based on a hierarchy of accounts. I can order and index my data in Power Query aggregating the forecasts, and I can order data in the...

Hi, I have the following macro below. What this does is loads a specific sheet from from s separate closed excel file. The issue is, the sheet name has to be "Dynamic 1" in the closed model. Is there a way to create a cell with a named range, and whatever sheet name the user inputs, that is the...

Hello, I was recommended to this forum by a friend who spoke very highly of it, so I hope I've landed in the right place! I am looking to put together an excel sheet to predict the scores, win probability and cover probability of american football. I would like to do this for other sports as...