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.
115
questions
12
votes
4
answers
86k
views
Excel 2010 PowerPivot: How do I show items with no data?
In standard Excel pivot tables, there is an option for fields that allow you to force display of all items even if there are no results for your current selection. Here's the option:
However, using ...
- 4,255
4
votes
1
answer
2k
views
Auto formatting new pivot rows to data bars
I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately ...
- 171
3
votes
1
answer
2k
views
Changing font size in Power Query
I have started to fiddle with Power Query a bit and thing that is pretty annoying is size of the font in formula bar and advance editor. Its way to small. Also when you manually enter formulas in ...
- 499
3
votes
2
answers
336
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 ...
- 546
3
votes
1
answer
5k
views
calculated column in pivot table from power pivot
From SQL server I have a query that gives me daily transaction numbers for few companies per day, something like
DATE Company Total Tax year
2013-04-24 ABC 50 2013
...
- 171
2
votes
2
answers
4k
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 ...
- 121
2
votes
1
answer
66
views
With PowerPivots, find items that appear *only* in one category
With PowerPivot, how do I find items only in one category? Here's an example:
item category number
obera f6 7
artois f7 2
obera f7 3
ella f6 9
If ...
- 947
2
votes
1
answer
8k
views
How should I share workbooks that use PowerPivot?
I've used powerpivot to make a super-awesome report. But now I need to share it with my co-workers, who do not use powerpivot.
When I send someone a powerpivot report to users without powerpivot ...
- 423
2
votes
0
answers
150
views
Ragged hierarchies in Power Pivot
In Power BI, one can use the Hierarchy Slicer vis from AppSource to hide items that contain blank values:
Is there a feature similar to that, but in Power Pivot? I want to hide the blank values, but ...
- 21
2
votes
1
answer
516
views
How can I publish excel workbook with data populated using PowerQuery to SharePoint or website and have the data refresh on the site?
I have an Excel Workbook that gets data from an API using PowerQuery, to populate the tables. There are also filters and slicers in the workbook.
Is it possible to publish this workbook to ...
- 21
2
votes
2
answers
4k
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 ...
- 21
1
vote
1
answer
1k
views
Excel 2010 PowerPivot: How do I avoid apostrophes being displayed as ’?
When I import a text file to PowerPivot containing apostrophes, they are automatically converted to ’.
I've been manually correcting the few that I find in the PowerPivot window, however when I do ...
- 4,255
1
vote
1
answer
1k
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 "...
- 175
1
vote
1
answer
38
views
Powerpivot: how do I avoid measures from computing until I'm done entering multiple measures?
With Powerpivot, I have many measures (>25) to enter in a dataset that has millions of entries. When entering the DAX expressions for the measures, each time I enter one, Powerpivot takes a long time ...
- 947
1
vote
1
answer
9k
views
How to display non-hierarchical attributes on same row using Excel Powerpivot?
Using an Excel 2013 ProPlus PowerPivot pivot table, I have the following: (+ indicating a drill-down point)
+Attribute-1
+Attribute-2
Attribute-3
Attribute-4
Below is what I would like to ...
- 113
1
vote
1
answer
4k
views
Power Pivot DAX dynamic Percent of Column
I just started using Power Pivot and Dax. Today I learned the benefits of creating my own measures (counta, etc) vs creating them directly in the pivot table. So I thought I could do the same thing ...
- 257
1
vote
1
answer
3k
views
Excel: How can I create a slicer based on data from multiple columns
I'm using PowerQuery and Power Pivot to analyze some data. I would like to create a slicer based on data from multiple columns. Typically slicers are tied to a single column.
The below table is a ...
- 68
1
vote
1
answer
254
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 ...
- 169
1
vote
1
answer
1k
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 ...
- 314
1
vote
1
answer
33
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).
...
- 33
1
vote
1
answer
103
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 ...
- 720
1
vote
1
answer
951
views
How to use PowerPivot in Excel 2013 (Home and Business)
Is there anyway to use PowerPivot in Excel 2013 (Home and Business)?
- 113
1
vote
1
answer
251
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 (...
- 11
1
vote
1
answer
84
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 ...
1
vote
1
answer
2k
views
Import from Folder data source missing from PowerPivot
If I look around on the Internet, I see people have the ability to import all files from a given folder:
http://cwebbbi.wordpress.com/2013/03/01/importing-data-from-multiple-log-files-using-data-...
- 458
1
vote
1
answer
3k
views
Data Source Credentials issue with Excel PowerPivot
I am using Excel 2010 with PowerPivot. I am trying to go to the Query Editor. When I click on Table Properties, I get a password prompt:
The problem is that I don't know the password and when I press ...
- 458
1
vote
1
answer
757
views
PowerPivot data feed error "Semicolon expected" during import
When importing data from an atom feed in PowerPivot, I get the following error:
XML parsing failed at line 7, column 727: Semicolon expected.
The feed currently only returns two entries, and ...
- 206
1
vote
1
answer
2k
views
Excel PivotTable % of a specific value
My users have a hand built report showing tickets by time frame and their priorities.
Priority Count
1 68
2 41
3 61
N/a ...
- 365
1
vote
1
answer
79
views
Excel Pivot Table showing same count as the first column
I'm a beginner at Excel and am currently practicing pivot tables.
The data that I'm dealing with is this table:
In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...
- 11
1
vote
0
answers
35
views
Using a seperate date for FTD outside of datamodel in excel report
I have a data model named Sanction in the following format.
Sl. Date Loc Product Num Amount
1 01-06-2023 Loc1 Pro1 2 5
2 02-06-2023 Loc2 Pro2 5 6
I am ...
- 119
1
vote
0
answers
575
views
Show values in PowerPivot only for total/subtotal
I have a value column coming from simple count() in PowerPivot Data model.
Is there any way to show count value only in subtotal/grand total rows, without displaying value 1 in each individual row?
I ...
- 11
1
vote
0
answers
22
views
Create a custom pivot table off two different pivot tables
Suppose you have the following 2 tables from 2 different sources of data
Table 1
Table 2
I want to create a custom pivot table off these two tables, which will look like the following
Note that the ...
- 31
1
vote
1
answer
169
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 ...
- 11
1
vote
1
answer
187
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 ...
- 11
1
vote
1
answer
134
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 ...
- 11
1
vote
1
answer
417
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"))
- 11
1
vote
0
answers
249
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...).
The table in PowerPivot view:
I can't figure out a formula that properly ...
- 13
1
vote
0
answers
32
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 ...
- 343
1
vote
2
answers
5k
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 ...
- 11
1
vote
0
answers
1k
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 ...
- 141
1
vote
0
answers
1k
views
Excel 2013 Workbook Size Optimizer not working
I have a large PowerPivot/PowerView workbook that needs to be cleaned up. Microsoft offers a free tool called the Workbook Size Optimizer for situations like this, but I have not been successful in ...
- 91
1
vote
1
answer
213
views
Powerpivot install fails
My machine runs windows 7 professional (64bit) and I use MS office 2010 professional 32 bit.
I'm attempting to (re)install the powerpivot plugin but i get an error stating "installation of ...
- 11
1
vote
1
answer
4k
views
Listing/grouping variables in PowerPivot table field list
I have data with 60+ variables and I would like to group the variables into different collapsible fields in a pivot table, similar to having different tables in the pivot field list - ie, instead of ...
- 13
1
vote
0
answers
202
views
PowerPivot - Your beta software has expired
I'm running the 32-bit version of Excel 2010 on Windows 7 64. I've downloaded the 32-bit version of PowerPivot from here however I get the following error:
Now I can see what the error message is ...
- 119
1
vote
1
answer
411
views
How can you improve an Excel workbook that leverages PowerPivot
I have an Excel workbook that has 30 plus pivot tables and 10 plus slicers connected to PowerPivot. When we filter data based on these slicers the performance is sub par, 30 sec to 2 minutes. Is ...
- 1,109
1
vote
1
answer
67
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 ...
- 133
0
votes
1
answer
2k
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 ...
- 204
0
votes
1
answer
1k
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 ...
- 1
0
votes
2
answers
4k
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, ...
- 113
0
votes
1
answer
5k
views
How to use Get Data in Excel without loading the data fully in Excel
I have a minor nuisance regarding loading the data into Excel. I load data from a SQL database as follows: first go to the tab Data > Get Data > From Database > From SQL Server Database. ...
- 181