powerpivot

Hi, I created two functions for cumulative total and percentage: CumulativeTotalDelivered:=CALCULATE([TotalItemsDelivered],FILTER(ALL(dCalenderDel),dCalenderDel[Date]<=MAX(dCalenderDel[Date])),VALUES(dCalenderDel[Date]))...

If I add a calculated column to a table with PowerPivot, and the table in PowerPivot is linked to its counterpart in an Excel worksheet, how can I show the PowerPivot-calculated columns in the table in the Excel worksheet? I understand that Power Query has the capability to do this, but I do not...

I attempting to use PowerPivot to calculate our group standard cost across a number of plants globally. The trick is that often we have intercompany (inter-plant) materials whose standard cost is that of the selling plant's. The calculation of this is causing some circular references, despite me...

It seems like such an easy thing: As I would do in excel, I just want to use a cell in my row to determine which column to return value from. An example of my (VERY simplified) table "tTable" is: <tbody> Part Default Vendor Supplier1 Supplier2 Supplier3 Price @ Default Supplier Material1...

Hi there, I'm utilizing powerpivot to perform cost center allocation. I have a transaction table which was loaded to data model and another table containing % of allocation to each receiver cost center # was loaded to data model as well. Required relationship was established using cost center #...

Good day, I thought this would be easy but I'm having a devil of a time, I hope someone can please help! I've tried writing this in a table; within PowerPivot; and as a measure but nothing seems to elicit what I'm after. (I'm new to PowerPivot, so I may be overlooking something obvious.) I'm...

Hello, I am having issues using the RANKX function in my PowerPivot data model. I am trying to rank two different metrics by region, territory: sales and growth from last year. The sales rank works perfectly, but the growth rank seems to skip a rank when growth turns from positive to negative...

Hi all I am new to powerpivot. I tried searching the forum and in google for an answer to my question but I couldn't get an answer. I am hoping an expert in this forum can help me. So I have an existing data that I pull using SQL and then created multiple Pivot tables from this SQL data...

I'm looking at calculating how many days a ticket has been open and the average days all the tickets have been open for that month and group. I have a date table that is not connected a ticket table <tbody> Ticket Group Created CloseDate Broken Screen West 8/24/2017 4/26/2018 Broken Mouse...

Hi Team, I am not able to see PowerPivot option in my MS Office Standard 2016 edition and only can see Power Maps. Is there any way to tweak or install the Add-In to get this option in Excel 2016. Is there any other option like the system which has power Pivot add-in and this addin can be...

Hi! This has probably been asked a couple of times, but i couldn't find the correct thread... Apologies for that! I often use =IF(X;SomeNumber;"") When using the resulting sheet as a linked table in PowerPivot it interprets "" obviously as a string and forbids me to set the data type to...

Microsoft has released an Office 2019 preview for business. I looked under the what's new section for Excel, and it says "PowerPivot enhancements". Does anyone know what those enhancement are? Assume they are in line with Office 365 Excel. Thanks.

I have a PowerPivot file where I have Power Query sort by date before entering PowerPivot. Then in Powerpivot I have added calculated columns, a few of which I moved to be in earlier parts of the table instead of the end. However, when I go to Excel, the columns don't match the order that I...

I am working with an involved PowerPivot data model in Excel 2010. This model has multiple sources of data and numerous pivot tables. Some of the people on the distribution list use Excel 2016 so to make their lives easier, I am providing a "pre-upgraded" workbook for Excel 2016 for them. I...

I created a PowerPivot table. Below the table, I included a few rows of calculations (outside the table range). When I refresh the PowerPivot data source and table, if the number of rows within the table increases, my formulas below the table are overridden. Is there a way to instruct Excel...

Hey Guys (and Gals), I currently have a daily report that imports data (in table form) from a file that is placed into network folder every morning (I did this using Powerquery). So, everyday I go into my report, hit refresh, and the table is automatically updated from the file sitting on the...

Hi, I need some help. I have invoices from customers listed out in separate worksheets, one for each month in 2017, 12 total. About 200k rows of invoices in each sheet. So, I imported each worksheet into the data model of a new workbook (using Power Query, Get and Transform). But I'm having...

I am using Excel 2016 PowerPivot Pivot table and in page field some items are filtered. Then I double click some numbers in the table and this warning prompt comes up "Show Details cannot be executed when multiple items are selected in a ..." Hey but there is no such irritating error when I...

Hi, I'm trying to create a formula in DAX for: If location ="9000" And the date is between 4/1/18 and 6/30/18 Then take the SALES_GOALS[LY CORE]*1.5 =if(AND(SALES_GOALS[STORE]="9000",SALES_GOALS[LAST YEAR DATES (Month)]="apr"),SALES_GOALS[LY CORE]*1.5,SALES_GOALS[LY CORE]*1.1) I've got it...

Trying to have a measure provide the same results as only the Grand Totals of the following vanilla PivotTable. Table AcctList Fields, [DueDate], [Amount], [Name], [Validated/Held] Page Filters = [Validated] = “Validated” Rows = [Name] Columns= [DueDate] Sigmas = Sum of [Amounts] Row Filter =...