# 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.

104
questions

**0**

votes

**0**answers

19 views

### Importing huge dataset held in POWER PIVOT table to Microsoft ACCESS

Background
I am trying to make a database, but am having issues importing data into microsoft access. I have huge files, 4000,000 rows long and each one needed merging with much smaller tables.
What ...

**1**

vote

**0**answers

31 views

### Distinct Count of Items Having Distinct Count of Other Items

I have data that look like this:
What I want is a distinct count of the number of dates which have a distinct count of Person equal to two. The answer should here be one.
I am in Excel Power Pivot ...

**0**

votes

**2**answers

73 views

### Excel : list of installed base

Thanks in advance for helping me out!
How would you advise me to go from this table:
Equipment Desc | Ship To Name
------------------+----------
Small | Bob
Small | Dylan
...

**0**

votes

**0**answers

28 views

### How to Add custom Categories to Transaction Descriptions in an Excel Data Model

TL;DR: (How)Can I create a pivot table from a data model in Excel where I can add custom text to that table, and then feed that modified pivot table back into the Data model?
Background:
I am new to ...

**0**

votes

**0**answers

23 views

### Change Excel Slicer from “OR” to “AND”

I'm trying to graph the number of people who have a certain skill set, using a slicer. The problem that I'm running into is that the slicer tool, by default, will show me ALL of the records that ...

**0**

votes

**0**answers

21 views

### Find the last balance quantity per customer per month (Power Pivot)

I have the below dataset (Table 1), and I would like to create a new calculated column to only have the last Balance Qty per month for each customer.
The last column (EOM Balance Qty) illustrate ...

**0**

votes

**0**answers

5 views

### Power Pivot Measures

My Office version is 2013, everything fine except with measures that were in cells not showing output computations, but only text of name of measurement.
Anyway to get pass this issue by any chance, ...

**0**

votes

**1**answer

214 views

### Excel Power Pivot Data model Sum of Max date groupings

I have a dataset in Excel that I need to summarize. The source table is in a format like below:
Table example
The data extends for years and includes a heap more metadata columns.
The value in each ...

**0**

votes

**0**answers

36 views

### MS Excel. How to analyse dates when they are heading names?

I've been given a data set that consists of hundreds of Stores and almost a hundred dates, below is a small sample. The issue is that dates (mmm-yy) are actually heading names. The reason I see this ...

**0**

votes

**0**answers

38 views

### MS Excel. Slicer alternative when dealing with hundreds of values

I'd normally use a slicer but in this instance I have almost 300 options and a slicer won't be very user friendly.
Can someone please suggest an alternative?

**0**

votes

**1**answer

37 views

### How to make a DAX measure NOT to re-calculate on aggregated levels?

I have a DAX measure which works perfectly on the level 0 POV in the Pivot Table but I was wondering if there is a way to make it aggregate like a regular number when filtering to top level POV (e.g. ...

**1**

vote

**1**answer

38 views

### Replace Static Table Data w/ External Source and Update Pivot Automatically (Excel)

st tab is a table with data manually pasted from a file
nd tab is a pivot based on the 1st tab
Is it possible to replace the 1. st tab with an external data source (load-in the file without pasting ...

**0**

votes

**1**answer

19 views

### Excel PowerPivot - Working out percentages for columns in a PowerPivot

For illustration, I have two columns:
Col1 | Col2
------------
A | Y
A | Y
A | Z
B | Y
B | Y
B | Z
My Pivot looks like the following (added Col1 as a Row and ColB as a column)
Col ...

**0**

votes

**0**answers

14 views

### Not able to install power pivot add in in excel 2010

This message is showing when i double click on setup.
please help me to install it .I have read all the installation instruction . My pc is 64 bit and i have downloaded 64 bit power pivot.

**0**

votes

**0**answers

24 views

### PowerPivot get the last value recorded for a date

I have a PowerPivot query where I have 3 columns: ID, Date and a Value. Every day is possibly recorded multiple values each on new row. so it looks like:
ID Date Value
1 1/1/2019 654594
...

**0**

votes

**0**answers

16 views

### Change PowerPivot source from External excel to internal table in document

I have a PowerPivot connected to External Excel data file. This powerpivot file has a calendar and tons of created measures. I am trying to make things more efficient and want to change the source ...

**-1**

votes

**1**answer

25 views

### Create table comparing data from multiple date periods

In Power BI I've made a table where I'm comparing values from different date periods. For example, I have the following columns:
Last 7 Days
Last 14 Days
Last 30 Days
Last 60 Days
Lifetime
So I'm ...

**0**

votes

**1**answer

331 views

### Struggling to import Oracle tables via Excel Power Pivot

I have an issue where i'm trying to pull some data from an Oracle table via Excel Power Pivot. To connect to Oracle, i used Table Import Wizard in Power Pivot and entered all the required info (...

**0**

votes

**1**answer

450 views

### Excel 365 - Pivot tables ungroup dates when refreshed; not an issue in Excel 2016

I have an Excel workbook that is pulling data from MS SQL Server into a PowerPivot data model and then about a dozen pivot tables. These tables contain date information that is grouped by year and ...

**0**

votes

**1**answer

40 views

### Using a slicer to choose one column of a power Pivot table to exibit

I have a pivot table that summarizes a list of properties of many furnaces over a period of time.
Each line is a date and each column is a property.
I have dozens, and properties, would be unpractical ...

**0**

votes

**0**answers

243 views

### Excel Pivot - Group by year and week

I can group data by YEAR. I can also group it by WEEK. However, I can't seem to group it by YEAR and WEEK.
I used https://www.extendoffice.com/documents/excel/1874-excel-pivot-table-group-by-week....

**1**

vote

**1**answer

43 views

### PowerPivot - How do I establish a relationship between a table's columns to another table?

I'm beginning my first PowerPivot, but I'm starting to think my setup is completely wrong, or maybe PowerPivot isn't the right approach.
I have two tables: A huge table of wages per week for multiple ...

**0**

votes

**1**answer

18 views

### Excel: Add 3rd “values” column to show percentage of one column against another

Please see attached - how would I get another column in to show the 3rd column as a percentage of the 2nd column?
I tried https://www.contextures.com/xlPivot10.html but couldn't see one that would ...

**0**

votes

**1**answer

332 views

### Difference between two averages in excel power pivot

I have the following data table in excel, showing how many apples i collected in the first 12 hours of the day, and how many oranges i collected in the last 12 hours of the day, for the 1st, 2nd and ...

**0**

votes

**1**answer

353 views

### PowerPivot returning all values

I am trying to learn with PowerPivot and from what I saw, it would be THE tool to help me reconcile similar data in two different systems. However I cannot seem to get through the basics - I have ...

**0**

votes

**1**answer

491 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 ...

**1**

vote

**1**answer

67 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

837 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 ...

**1**

vote

**1**answer

55 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

576 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

321 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"))

**2**

votes

**2**answers

118 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

26 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

1k 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 ...

**1**

vote

**1**answer

3k 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

109 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

7k 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

78 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 ...

**0**

votes

**1**answer

962 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

224 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

430 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

519 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

777 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

801 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

22 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

52 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

37 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

259 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

27 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

599 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 | ...