powerpivot 2016

Hi guys, I have two tables: Master Table: contain the list of all customers we need to monitor the sales Sales Data: we update it every week with year to date sales for the customers in the master table MASTER TABLE SNIPPET Customer ID Customer Name 12345 Customer 1 12346 Customer 2...

HI There I have a power pivot (also in powerquery) table ProcessData that includes 3 columns: UniqueItemRef; Status; Update (=datetimestamp) If an item has passed through several statuses it will have several rows for those statuses (same UniqueItemRef and Status on each row) Also, if...

Hi All, Is there a way to directly connect PowerPivot to a SharePoint list? I've searched for a while now and can't seem to find a way. I have exported the SharePoint list to an excel file and I have connected the PowerPivot to that file, but it means refreshing that excel file and then the...

Background: I've configured a PowerQuery to automatically fetch a sales and forecast dataset that updates monthly. So, the dataset gets arranged based on the Source File which gets refreshed on a monthly basis. Example: Feb'19 Dataset: Has Jan'19 Sales Actuals and Feb'19~Dec'19 Sales...

Hi, would appreciate any help on this... I have a PowerPivot which imports 1,988,837 rows from an external connection, it gets to 1,980,000 but doesn't complete. I'm running 64bit Excel with 16GB RAM, Task manager shows RAM and Disk use near 100%. Is there a way to import the Rows before it...

I'm new to much of this, so please bear with me. . . I'm trying create a count of active projects by week using projects' start and end dates. I've created a COUNTIFS formula in an Excel worksheet table that works, but I would prefer the calculated column reside in my Data Model/PowerPivot...

Hi - I'm currently using Excel 2016 Professional Plus. I have a workbook that contains a large data model. I have 20+ reports I need to create and this data model will be used as the repository that supplies all the information for those 20+ reports. However, every site/blog I read (granted...

I am currently looking at a transaction list data set where each line shows a location with an amount sold in an adjacent column. I have found out how to rank the values data so I know the amount the top location sold using =MINX ( TOPN ( MIN( 'Rank'[Order] ) ,VALUES (Location [Provider short...

Hi Apologies if it is a stupid question, is there a way to sort two columns in PowerPivot ? For example, sort column 1 by High-Low, and then still preserving that sort, move to column 2 and sort that ? Column 1 could be customer number, and then column 2 Date, so all customer orders together...

I need some help. I am trying to build some pivot charts in excel, the problem i am having is I have a dozen or more columns of data that I need to toggle on and off. I have my date as the row, and I have a dozen different variables for each date. I.E Nitrogen, Potassium Sulfur Ect. I would...

Hello all. I'll explain what I'm trying to do, I'm unsure if I can achieve this with Power Pivot, I'm fairly new to PPvt & gotten to grip with the basics. So my source data looks like the below, which is many 000s of rows, formatted as a table & is in my data model. <tbody> Data...

Hi all, Is there any way to add calculated row to another pivot table as on the picture below?: https://ibb.co/eicYP6 I need this messure to add a target realization chart (is calculated in pivot by formula % of Paid on time (all):=([Total paid]-'Paid invoices'[Paid not on time...

Hi, When I want to edit the table properties of my SQL table in Powerpivot, I used to be able to preview the table. Now per default I have the query editor and the options are greyed out. I already found on the net that this is a bug, but I can't find how to solve it. Can anybody help? Thanks...

I have following table. <tbody> Date Segment Quarter No of stores No of sales 31/12/2017 America Q1 100 45216 31/03/2017 America Q2 200 6589 30/06/2017 America Q3 300 2388 30/09/2017 America Q4 500 6358 </tbody> I want to display year end figures in the pivot table. I have created a...

Hi I have an excel workbook that uses SQL to grab all our data from our Server and then I've built about 100 measures and wonder if it is going to slow down our file to much. Also Is there a better way to organize all the DAX functions than just putting them at the bottom of a PowerPivot tab...

In powerpivot excel 2016 i write a formula to sum year to date sales using filter function as below: SalesYTD:=CALCULATE ( [Net Sales], FILTER ( ALL ( Sales), 'sales'[Year] = MAX ( 'Sales'[Year] ) && 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ) ) And it's work perfectly now in...

I like to pivot a datatable with a lot of values per row (for each product and day) to a single row per customer. now I make a pivot table to excel to do this, link it to a table and bring it back to powerpivot. then I can link it to other tables due to the unique ID see image: this side step...

Folks, I am running Win10 and Excel 2016. I have enabled the PowerPivot Add-In. The I went to PowerPivot - Manager to get external data. As soon as I browsed to the file I wanted and selected it the file started to load without allowing me any other actions. Excel then stopped responding. Any...

Hi All, I am trying to create a model to evaluate participant data and then create a series of charts and tables to correlate that data output to a specific target date mutual fund series. I have the logic thought out, but am having trouble executing this with powerpivot. I have 3 tables and...

I'm trying to build a pivot table that will allow the user to select one department and it will show all say 3 data fields for that department. In some cases, however, the department they select will have sub departments. There is data at both the department level and the sub department level...