Stack Overflow
Stack Overflow

Questions tagged [powerpivot]

An in-memory OLAP cube that can for instance be installed and used in Excel. It allows for fast analysis of huge datasets.

PowerPivot is part of the Microsoft Business Intelligence suite and uses SQL Server in the background, but it is geared towards the data-savvy business oriented person. It allows you to easily load data from different sources, put them in a relational database-like format and create relationships between the tables.

When all data is loaded, an OLAP store is created in the background and you can do real time analysis by selecting and filtering to your heart's content.

Also included is a powerful query language called DAX (Data Analysis eXpressions) which use an Excel style syntax to allow the user to perform complex calculations.

Power-Pivot datasets can also be served as part of SharePoint and Microsoft has even put at least one all-in hardware appliance on the market in collaboration with hardware vendors.

The following links provide useful reference information:

Further interesting PowerPivot articles can be found here:

1828 questions
0
votes
1 answer

Conditional Probability with Powerpivot

my major goal is to calculate Conditional Probability over a large number of rows. Hence the use of Powerpivot. Attached is an excel file with 10 rows as an example of how I did it in Excel. My challenge is the formula in column F which I will then…
Tamir
  • 33
  • 5
0
votes
1 answer

PowerPivot Measure Count Multiple columns with a filter

I'm using PowerPivot within Excel and want to get the sum of counts for multiple columns based on a filter. My current equation is: Measure1:=COUNT(Table1[Attribute1])+COUNT(Table1[Attribute2])+COUNT(Table1[Attribute3])+COUNT(Table1[Attribute4]) But…
MarkO
  • 63
  • 2
  • 10
0
votes
1 answer

OLEDB change connection error 1004

I have done some investigation on this issue, but nothing works out. I am trying to change my PowerPivot connection. Im able to change Access source file, but on refresh it gives me an error, because the workbook(highlighted) connection string…
grechill
  • 1
  • 1
0
votes
1 answer

Single distinct count measure on a single table based on two filters - each filter value on distinct rows of the single table

I'm trying to demo some more complex DAX / powerpivot concepts to people on my team. I came up with an example to walk them through, but I think might actually be impossible. Happy to change the example to something else, but this has really piqued…
dijksterhuis
  • 1,225
  • 11
  • 25
0
votes
1 answer

Working Days in DAX

I have a requirement where I need to find the working days of a store. The formula to find the working days of a store is:- If Store is opened on or before first day of respective month then Working Day = Total Days in a month. If Showroom is…
BIDeveloper
  • 767
  • 3
  • 14
  • 28
0
votes
1 answer

Using DAX, how do I calculate a table when filtering on a related table?

I'm trying to find the intersect of two result sets using DAX, but I'm really struggling to get the two result sets calculated. I have a fact table, FactCheckForUpdates, that has a relationship to a date table called 'Log Date'. FactCheckForUpdates…
Jonathan Harding
  • 3
  • 2
0
votes
1 answer

Reorganize Data Excel

I need to take the following(CSV): deviceId,ip,mac,serial,version,platform,vtpDomain,stack num, stack ports, stack model, stack version, stack image, stack MAC, stack Serial AA_MDF_2960X_2,10.10.10.10,abab.abab.abab, 123123123,15.0(2a)EX5,cisco…
Michael Fisher
  • 25
  • 6
0
votes
2 answers

Power BI - DAX for Rolling Sum (Running total) not working,

I'm trying to calculate the rolling sum in the MS Power BI application using DAX. I've got the following table, and adding another column which would display the running total of the [hours] column, But I'm only getting 1 distinct value to each…
lukieleetronic
  • 623
  • 7
  • 10
  • 23
0
votes
1 answer

How do I rank customer visits by date in PowerPivot?

I have a table with customers' transactions named "purchases" with fields like this: -------------------------------------------------- | title | price |qty| client_id | created_at | -------------------------------------------------- | product A…
Arseniy
  • 487
  • 4
  • 14
0
votes
1 answer

simple PivotTable with relationship not aggregating correctly

I have two tables in my data model. You can call them Customer and Order and they are related by a key CustomerID, so the model could look like this: Customer: CustomerID CustomerName Order: OrderID CustomerID OrderTotal There is a relationship…
vcraigc
  • 91
  • 1
  • 8
0
votes
0 answers

Date parameter in PowerPivot

I'm trying to figure out if it's possible to do this in powerpivot for Excel. In my SQL server datasource, I have a table that has a payment date field and an Amount field. Now I want to have 2 calculated fields: Pending amount and Paid Amount. I…
Jack Casas
  • 914
  • 18
  • 37
0
votes
1 answer

Powerpivot - Create flag if the row value of one column exists anywhere in another column

What I'm trying to accomplish doesn't seem too tricky, and should be doable I just can't seem to crack it. 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…
James M.
  • 3
  • 3
0
votes
2 answers

POWERPIVOT - Look up max value for a new column from existing columns

I'm essentially trying to create a new column that has a city given MAX dollars spent in a city for a given program, and that the CITY value isn't "BAD". Data example below PROGRAM | CITY | DOLLARS SPENT | NEW COLUMN 1 | X | $20 …
James M.
  • 3
  • 3
0
votes
1 answer

DAX between (not directly) connected tables

I have a model of 3 tables: I want to calculate the number of years a pilot has been active (i.e. at least 1 flight per year, regardless of how many flights): I thought that dragging the PilotName along with the below DAX measure would solve my…
Victor
  • 1,163
  • 4
  • 25
  • 45
0
votes
1 answer

Clearing Power Pivot Slicers

does anyone know a good way of clearing the slicers in a workbook. I have a sheet with many slicers and I would like to have a single button to clear them. I wrote a bit of VBA to do it but it’s a little slow : Sub ClearSlicers() Dim cache As…
MartinHayes
  • 23
  • 2
1 2 3
…
99 100