powerpiviot

Hi all, I'm building an aged debtor report using multiple tables. I've used Power Query to format and transform all my tables and have now pulled them all into the data model so I can create relationships in Power Pivot. My data table is a list of outstanding invoices, with a field called...

Hi I'm wondering if it is possible to use the DATESBETWEEN function using dates from 2 cells. I would like to run my inventory as of a date last year. I will be taking the current on hand, + units sold today to last year, - units received since start date. I would like to have a cell that the...

Hi I'm look to get the MTD sales for the same days last year. IE May 1-8 of 2016 vs. May 1-8 2017. Or in a perfect world May2-9, 2016 vs. May 1-8, 2017 This gives me all of May last year: 1 YR AGO SALES NET RETAIL:=CALCULATE('tblInvSkuMaster'[SALES TOTAL NET...

Good evening everyone, I have a model from powerpivot which its fact table has many colums. I wish to insert multiple pivot tables, each with unique columns. I know I can hide tables and columns from client tools, but this will hide the columns or tables I select for all the pivot tables I...

Hi I've created a inventory worksheet. I've tried to used power query/powerpivot as much as possible. But have no choice it would seem but to use INDEX and MATCH in 30,000 cells. Would that make the sheet really slow? I need to link product, sales, receiving and transfers all together. But...

Hi, I have a measure in powerpivot: Mix % Measure Avg:=averagex(DISTINCT(Forecast[Product]),Forecast[Mix %Measure])/100 I need to use another distinct measure in this for a column called (Forecast[Submission]). This would mean the measure takes the average of each individual product as it...

My company works with a crm system that is not in-house. I have tried to convince the supplier to give me access to a “real-time” copy of the database. I would like to access all our information to work with in Excel/PowerPivot and in Power BI. I have 3 questions I would need some help with. I...

Currently, I have the following table Company---------Date--------Exchange-------Size A-------------------2000----------------A-------------50 A-------------------2001----------------A------------ 100 B-------------------2000----------------B------------450...

When I create a relationship between two tables, it says each column has duplicate entries. How can I delete them? I just have to check my primary key column and remove rows of duplicate entries. Thanks in advance

My current table looks like this Company-----year----size-----Exchange A---------------2000-----80-------A A---------------2001-----85-------A B---------------2002------90------C I want to allocate the companies into two categories "Big" and "Small". For a particular year, if the companies...

Hi! Please help me with my thesis research. I have downloaded huge data set to analyse. I heard it can be managed via Power pivot tool. But I do not have intensive knowledge on this. For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in...

Hi, Not sure if this is possible. I have currently working the below formula: Revised MAD Measure:=AVERAGEX(DISTINCT(FAReportInput[SKU]),FAReportInput[Revised MAD Calc]) This essentially in my workbook takes the results of a calculation (the revised MAD calc) and calculates the results as an...

Hi guys, This one has being frustrating me for the last few days now. So I have a simple set of x,y map coordinates in 2 columns of a table with a yes no result in a 3rd. What I would like to be able to do is for each x,y result that is yes look at the square to north and see if that is a yes...

Hi, I just installed powerpivot yesterday thinking that it might be able to solve a problem that I am facing. Also, I am not sure whether the task I am looking at doing is even achievable in powerpivot. I have two tables. Master and Sub. Both of which are hundreds of thousands of rows. The...

Hello, So I am trying to combine columns in powerpivot to create the whole invoice date. I have three columns that have invoice year, invoice day and invoice month. I want to combine it to give me the full date in one column so I can be able to filter by a date range. What function do I use? I...

Hey There Our sales director would like a worksheet that shows the change of inventory over time ex: what did was the total stock of Product A at Dealer A Yesterday and What is the the total stock of Product A at Dealer A Today to see the change of inventory. So I'm thinking we would need to...

Hi everyone, I asking your help since I can't find a solution. I am working on a Financial scorecard. I am using Excel 2013 with powerpvot. I want my users using the new feature timeline since it is really sharpe. However, when someone select 2014 January, I also want 2013 January in order to...

About a week ago, the preview of measure values in Visual Studio shopped showing, it just shows the measure name. They are there in the data and when I preview in "Analyze in Excel" everything is fine. Then later in the week, some but not all of my "related(...)" columns started being blank in...

I am just getting started with PowerPivot and reading PowerPivot for Excel 210... by Russo and Ferrari. I have populated the PowerPivot Window with data exported from three Access tables. I now want to create a Pivot table from the PivotTable button on the ribbon. When I do that I get the the...