# Questions tagged [powerpivot]

PowerPivot is a data mashup and data exploration tool based on xVelocity in-memory technologies providing analytical performance to process billions of rows at the speed of thought.

**0**

votes

**1**answer

20 views

### How to use Power Query to create pivot tables with non-numerical values

The data is transformed into an Excel table and loaded into Power Query. It looks like this:
(I don't understand why the table doesn't work. After a few searches I realize that the site does not ...

**0**

votes

**0**answers

21 views

### Why are the totals of my Pivot Table wrong?

It is clearly supposed to add up but it isn't. I don't know if it has anything to do with my DAX formulas.
For instance, PagibigER's formula is: =if(sum(Input[Reg Pay])>0,100,0)

**1**

vote

**1**answer

59 views

### Combine none-unified data in excel based on row-id?

I have the need to combine different excel sheets of data into one single master file.
I have one master file with e-mail addresses and several excel-files from our CRM with data in different ...

**0**

votes

**0**answers

32 views

### Power Pivot SQL Monday as first day of week and display three char month name

I have the following 2 lines of code in an SQL query for Power Pivot:
,DayName=(DATEPART(dw,Transaction_Table.Date_of_Transaction)+5)%7+1
,MonthName=convert(char(3), Transaction_Table....

**0**

votes

**0**answers

19 views

### Power Pivot will not load in com add-in

Had a powerPivot application running perfectly with Windows 10 and EXCEL 365, but user has Office 10, so the 365 app would not run on that machine. I uninstalled Office 365 and installed Office 10 on ...

**0**

votes

**0**answers

42 views

### Vertical and Horizontal Slicer Boxes

I had to go back to Office 2010 from Office 365 in order to create a PowerPivot table for a user who has Office 2010. I had the thing running on 365 but it would not run on 2010. So I uninstalled ...

**0**

votes

**0**answers

23 views

### Why am I unable to use Ctrl+PgUp and Ctrl+PgDown to switch between tabs in Excel Power Pivot (2016)?

When I open the Power Pivot window, I cannot use the Ctrl+PgUp and Ctrl+PgDown shortcuts to switch between tabs.
If I switch to another window (Alt+Tab) and then switch back to Power Pivot window, I ...

**1**

vote

**1**answer

47 views

### Calculating differences between two sheets (efficiency issue) [closed]

Current setup to compare history on AR Financial Data:
-Two excel sheets with identical headers Range: A1 to AL250,000
-On one of the sheets, we added 13 Columns, which have equations doing ...

**1**

vote

**1**answer

127 views

### Is there a solution to the constant unhandled exceptions that Powerpivot throws in Excel 2013?

Each time I perform certain actions with Powerpivot in Excel 2013 it throws an exception about not being unable to cast a COM object to an interface. This happens most notably every time I try to type ...

**1**

vote

**1**answer

143 views

### How to do a distinct select with CountRows in excel?

I want to do a distinct select on the field Object_name with the below measure ,how would that be possible in excel ?
=countrows(filter(GDPR,GDPR[Object_Type]="Table"))

**0**

votes

**0**answers

15 views

### How to add a style to Excel power pivot value cells?

I'd like to add a vertical border to columns in a pivot table based upon their heading group. For example, if the heading is grouped by year, and then underneath the year, the values are further ...

**1**

vote

**2**answers

79 views

### Excel PowerPivot Relationships

I have an issue when using PowerPivot in Excel 2016.
After creating a relationship between two tables, and afterwards create a pivot table from the data model, the resulting pivot ignores the ...

**1**

vote

**1**answer

16 views

### Doing calculation in PowerPivot (Excel 2010) based on two tables

I am on Excel 2010 with PowerQuery and PowerPivot add-ins installed.
I have two tables, where:
Table1 has amounts denominated in different currencies.
Table2 has conversion rates (exchange rates).
...

**-1**

votes

**2**answers

451 views

### PowerPivot with Excel source file on OneDrive

Hi Could you please help me with a PowerPivot question?
The data file in Excel format is of huge size 500M+. It’s the source data and stored on OneDrive.
I want to create a Pivot table based on ...

**0**

votes

**0**answers

12 views

### Applying a flag/code to multiple rows (grouped by order) based on value in one row - Powerpivot

I have an odata feed from a system that provides a row per unique variant of information.
Here is an excerpt of the output file:
In this example, i would like to flag order 216100 as Paid since it ...

**1**

vote

**1**answer

1k views

### Cannot group field in Excel PivotTable

I know this question has been asked before, and the usual answer is "your dates are not all stored as dates!" I highly doubt this is the case here.
I'm using PowerPivot which pulls data from a ...

**0**

votes

**1**answer

39 views

### Why's power pivot measure area grayed out?

Can some one please help me? I am a new user of Microsoft power pivot and while I was working on my data, I realized the measured section is all grayed out and I cant enter formulas there. What am I ...

**0**

votes

**1**answer

3k views

### Cannot Install Power Pivot in Excel 2013 [closed]

I have a Windows 10 Enterprise Citrix machine that has Microsoft Office Excel 2013 Professional Plus installed.
From Excel when I click File > Options > Add-Ins I do not see a Power Pivot option.
...

**1**

vote

**1**answer

59 views

### In Excel, is it possible to create a graph for every category of data, from the same table?

I'm using Excel and PowerPivot to import some data from database, wich creates a single table (Sales). Let's say my table Sales has 3 columns: Sales, Month, and Category. There are 4 categories. I ...

**1**

vote

**0**answers

37 views

### Analyzing blood transfusion data; orders versus actual transfusions; Microsoft Excel 2010 [closed]

I have an Excel report that includes data for all orders for blood during a defined time period. One row is one order. There are columns for various patient demographic data (e.g. account number, date ...

**0**

votes

**1**answer

591 views

### Excel Pivot Table converting numbers to text in row labels

I have a calculation in a SQL Server View that returns an integer. Powerpivot retrieves this integer from the SQL view, and it appears to still be an integer (in Powerpivot). However, the Excel ...

**1**

vote

**0**answers

143 views

### DAX RANKX formula to skip ties properly

I have (video) games collection table [GC], with 'title, 'series' and various score columns (aggregate, experts, users...).
table in PowerPivot view
I can't figure out formula that properly skips ...

**0**

votes

**1**answer

137 views

### Power Pivot - Calculating a Defect Rate

Table 1 (DATA): Lists each held product for all of 2016
Table 2 (PRD DATA): Lists total good parts produced for each product in 2016
Table 3 (Label): Links the two tables based on the individual ...

**0**

votes

**0**answers

265 views

### Power Pivot - Count cells that contain specific text?

I keep trying but I would appreciate some help.
How do I do this https://exceljet.net/formula/count-cells-that-contain-specific-text
But with a PowerPivot?
I need this but in Power Pivot: =COUNTIF(...

**0**

votes

**1**answer

528 views

### Power Pivot Sort By Column Not Working in Excel 2016

I'm trying to sort a Month value (e.g. Dec-16) in a date dimension using a Month Sort column. I can set the sort by column in the data model no problem but when I use the Month value in a pivot table ...

**1**

vote

**1**answer

474 views

### How to use PowerPivot in Excel 2013 (Home and Business)

Is there anyway to use PowerPivot in Excel 2013 (Home and Business)?

**0**

votes

**1**answer

17 views

### Total open Qty for an Item

i have a table of open Purchase orders with multiple PO, an item can appear a number of times on any PO.
i then have a separate table for items that i am looking to buy but i want to know how many i ...

**0**

votes

**2**answers

47 views

### In a PivotChart filter, how do I select a just range in the column to display?

Help! I have created a pivot chart that shows 3 data series across iterations (time). All series are shown as 'running total in' base field: iterations.
The series are:
Total Hours Planned
...

**0**

votes

**1**answer

33 views

### why powerpivot completly brokes if there is no values?

Can't understand simple thing about PowerPivot.
Why this works:
When this doesn't:
I have just removed attribute "Earned" from "Values" group and PowerPivot completely brokes (in my opinion): start ...

**-1**

votes

**1**answer

200 views

### many to many in excel power pivot

I understand that working with many to many table structures in power pivots could be complex:
http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx?...

**1**

vote

**0**answers

24 views

### How to overwrite measures at a line level in PowerPivot

I have two tables, each of the same dimension and same level of granularity;
Date, Site, Campaign, Ad Group, Keyword are the columns, with spend as the single measure.
What I am wanting to do is ...

**0**

votes

**2**answers

528 views

### Powerpivot - Flag whether row value in one column exists in another

All I need to do is create a calculated column that flags whether or not the row value in one column exists anywhere in another column within the same table.
id | Code 1 | Code 2 | ...

**0**

votes

**1**answer

62 views

### Combine data from two seperate tables in Power Pivot without using a join based on concatenated key

I have two separate data sources with effectively the same columns
Date
Vendor
Campaign
Ad group
Keyword
Impressions
Spend
What I am trying to do is stitch the data together in a fact table in order ...

**0**

votes

**1**answer

1k views

### Names of the PowerPivot tables do not update in PivotTable

I have imported an Excel table into PowerPivot and renamed the tab in the PowerPivot tab. However, when I create a PivotTable in the Excel sheet, I can only see "Table1", etc. instead of the name I ...

**0**

votes

**2**answers

283 views

### POWER PIVOT formula to show previous day based on weekday

I have two columns called
1ST column Calendar Date:
16/02/2015
2nd Column Previous Day:
Yes
Formula I am currently using:
if([calendar Date])=today-1,"YES","NO").
The 2nd column returns value ...

**0**

votes

**2**answers

55 views

### defining a measure of unique value counts

How do I define a measure in a data model (withing the PowerPivot feature) with a formula that needs to get a unique count of values. For example, something like this:
reset_rate := UNIQUECOUNT([...

**0**

votes

**1**answer

460 views

### Excel Conditional Formatting disappears unless smaller dataset in filter

Conditional Formatting on a Power Pivot (Excel 2016 32-bit) using a Color bar is working nicely when results are page filtered to a smaller subset. But when nothing is filtered, the CF disappears, so ...

**0**

votes

**1**answer

523 views

### Excel function/Date Table - Defining week and period

Forgive me if this is a rookie question, however I'm kind of at a loss. I've been evaluating the PowerPivot tool for Excel, and one of the guides I came across with how to most effectively leverage it ...

**2**

votes

**2**answers

2k views

### Changing the Workbook Connection String in PowerPivot

I recently had to move a PowerPivot report from one terminal server to another. I was able to get the PowerPivot Data Connections up and running without much trouble. When I click Test Connection, it ...

**1**

vote

**1**answer

598 views

### 'Count Distinct' and Min(date) in the same pivot table?

Is there a way to have in the same pivot table two value columns, one of which uses Count Distinct and the other uses Min(date)? It seems that to have Count Distinct as an option I need to select "...

**0**

votes

**1**answer

192 views

### Loading data to power pivot via power query vs. directly to power pivot

If I'm fine with doing everything in PowerPivot, aside of future proofing in case I may want to edit queries in the future, are there any reasons to put Power Query buffer in between my data source (...

**1**

vote

**2**answers

1k views

### I do not know how to install Npgsql version 3.0.3

I want to install Power Pivot and Power Qwery in Excel for Postgresql. I installed Visual Studio and I installed Npgsql in Visual Studio PM (Package Manager Console). I can see, in Power Qwery ...

**1**

vote

**1**answer

56 views

### Calculate % of filtered values from all values in Excel Pivot Table

I have this pivot table that currently shows how many students failed in a particular subject in a particular academic year. That is, it is now filtered by Pass_Status Fail(which can be either Pass or ...

**0**

votes

**1**answer

778 views

### Is there a way to hide the Field List in an Excel PowerPivot table so that no users can see it?

I've got a dashboard created via PowerPivot that, when the charts are selected, allows users to open the PowerPivot Field List, so they can see things like, for example, the email address of the ...

**0**

votes

**1**answer

166 views

### Variables from Multiple columns lookup to insert new column in Power Query or Power Pivot

Apologies for the layout from below but it is a sample with 4 columns.
I need to insert the target from Table below in a new column, based on the Product (Quite a long list) together with the ...

**0**

votes

**0**answers

60 views

### Power Pivot for Excel - version 11 not Installing on Office Professional Plus 2010

I'm trying to upgrade the Power Pivot for Excel add-on from version 10 to version 11, but I keep getting the error that Microsoft Excel 2010 must be installed first.
The laptop is running on Windows ...

**0**

votes

**2**answers

1k views

### How do you group by week in Excel PowerPivot?

Say you have a simple sales table in PowerPivot. What's the easiest way to allow the data to be grouped by week in a PowerPivot pivot table?
I know excel's basic pivot tables have a group feature, ...

**1**

vote

**0**answers

963 views

### How to create a 100% stacked column chart if I have total

I'm trying to create a 100% stacked column chart using Excel 2013 Pivot Table to show what % of Column A is of a Total Column B.
However the 100% stack column chart doesn't seem to work if I have a ...

**0**

votes

**1**answer

35 views

### Change pivot table layout into “more columns”

When I create a pivot table in excel 2010 my table looks like that:
Any suggestions how to get this extra colume filled up with the names within a pivot table?
Or do I need to use power pivot? How ...

**0**

votes

**1**answer

703 views

### Two-way data update with Excel PowerPivot tables (Office 2013)

Background:
We have two excel workbooks: Data and Visual which we are using to track and visualize activities in our project. The Data file is organized like a database: each sheet is a table (which ...