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

**1**

vote

**1**answer

12 views

### Using SELECTEDVALUE with PowerPivot

I got help to the code below to create a measure in PowerPivot, but I'm running into errors because of SELECTEDVALUE which does not seem like a command I can use in Excel, anyone can help me around ...

**1**

vote

**0**answers

11 views

### How can I fix DAX XIRR calculation for projects with different investment start dates?

I am managing an enterprise strategy that is moving people and real estate across multiple geographies. The strategy is comprised of a large number of projects that can be considered independent of ...

**0**

votes

**0**answers

14 views

### How to add measure under specific category

Suppose I have sales data, by product, week, and year. Now I can drag product to rows section and week/year to columns section and then I can use the sales as a measure. However, if I created new ...

**0**

votes

**1**answer

47 views

### DAX help: % monthly share of another table

I have a DAX formula for my Powerpivot I cannot get to solve and was hoping for help.
I have two pivot tables connected already
Showing a cohort of actions taken within Month 1,….X on the sign up ...

**0**

votes

**1**answer

40 views

### Power Pivot - Dax Measure sums up wrong

together
somehow my pivot table is currently crashing. The table is structured as follows:
Area, Code and QTY1 are defined by the data model. QTY2, Min and Performance are measures.
QTY2 sums all ...

**0**

votes

**0**answers

16 views

### Power Pivot Runtime Error : -2147024882 Microsoft.Office.PowerPivot.Backend.PowerPivotFacade.GetFieldsInEr not found

Method not found for large data set. It was working on small data set
VBA-Code where error prompted
s.PivotTables("Fragmenter").Update

**0**

votes

**0**answers

21 views

### SUM two rows in PowerPivot Pivot table MDX

Really new to MDX and have researched for hours but haven´t found the way to make it work.
Objective: Sum two rows (liabilities-pasivos and equity-patrimonio) for a pivot table that represents a ...

**0**

votes

**1**answer

27 views

### How to sum two lookupvalue results in Power Pivot

In a sales report I want to sum the result of two lookup results. I have two tables that are related (Kürzel & Vertreter; Kürzel & Vertreter 2). However only one relationship can be active the ...

**0**

votes

**0**answers

32 views

### Reference a Column Header in a DAX Formula

In a PowerPivot calculated column, I'm attempting to LookupValue the hours in a month from a separate table, using the value in the previous column's header as my search_value. Is this possible?
I'...

**0**

votes

**0**answers

22 views

### How do I add Calculated Columns to a table in the PowerPivot Data Model using VBA?

I am aware that we can add/edit Measures to/in the Data Model from VBA using the ThisWorkbook.Model object. Is there a way to add Calculated Columns using VBA using the Model Object?

**-2**

votes

**2**answers

39 views

### Excel Convert Make/Model/Year Range into Individual Year/Make/Model rows

I'm attempting to systematically expand a table of Make/Model/Year Range combinations into individual year rows. I've tried looking for solutions but none seem to match my use-case and I don't know ...

**1**

vote

**2**answers

40 views

### Add calculated column

I'd like simply to add a calculated column to a pivot table that i got. This pivot table uses two sources of data. Each source provides one column to the pivot table. I wish I could divide one column ...

**0**

votes

**1**answer

29 views

### How I can find percentage out of subtotals with multiple columns in Pivot table

How I can find percentage out of subtotals with multiple columns in Pivot table.
PercentYes :=
CALCULATE ( SUM ( MyTable[value] ), MyTable[answers] = "yes" ) /
CALCULATE (
SUM ( ...

**0**

votes

**0**answers

44 views

### How to get distinct count within pivot table(Excel for Mac) having filters?

Excel for Mac doesn't support Power Pivot and thereby doesn't have distinct count feature.
What is the best workaround to get distinct count in such cases?
Sample Excel Columns:
Period Criteria1 ...

**0**

votes

**0**answers

11 views

### Combining data with different keys to a new table

I'm setting up a model which have 3 input files.
They are containing products, two of the files share key, let's call it supplier key, while the last have a unique key, called internal key.
I need to ...

**0**

votes

**0**answers

10 views

### Power Pivot data model and Web Form applications it is possible?

I have a Power Pivot Data Model created and I'm interested to know if exist the way to exploit the data on that model using a Web Form Application using ASP.NET
Do you know if that it possible? if it ...

**0**

votes

**2**answers

61 views

### DAX measure that returns the distinct number of values that have duplicates within the table

Fairly new and self-taught with DAX. I run an accuracy log that tracks incoming applications (Application[Application_ID]) and errors committed in processing that application (Error_Log[Application_ID]...

**0**

votes

**2**answers

33 views

### Dax Finding date based on Criteria calculated Column

I couldn't find an answer for my issue elsewhere, so trying my luck here.
I have sales table and my final result should determine if there were sales made for same person in specific period of time, ...

**0**

votes

**1**answer

25 views

### Excel Dax Count Unique Values and filter on current week automatically

I am pretty new in wokrin with data models within Excel, especially in Dax formulas.
I have the following two tables in the data model.
Table_HS:
Product Code - User
Table_IS:
Product Code - Work ...

**0**

votes

**1**answer

16 views

### DAX ISBLANK() Showing “False” for empty cells in Power Pivot

I'm looking to generate a calculated column based off of some other columns in a PowerPivot table.
The cells are blank - as in no discernible value - but when I reference them as BLANK(), my formula ...

**0**

votes

**1**answer

30 views

### Is there a way to define “% of column total” measure in DAX which would work dynamically for any dimension?

I need my measure % of column total to work with any dimension that is currently selected in the pivot columns.
I have this formula which works for dimension called Gender, but I need the formula to ...

**0**

votes

**0**answers

24 views

### How to Loop in Slicer in vba

Im trying to use an old code to run with another new slicer.
I need a code that loops in a slicer, for each selected item make a copy of this data for another sheet
Ive tryied the old cod, but my ...

**0**

votes

**0**answers

27 views

### Is there a way to get rid of #N/A error in CubeValue Formula when using a date range and a particular date isn't available in that range?

I'm using CubeValue formula to fetch "Volume" data from Power Pivot Data Model, however returning #N/A error because few of dates aren't available within the range specified.
If I'm giving range from ...

**0**

votes

**1**answer

46 views

### Calculate price based on distinct count

I am having trouble working out a measure (Revenue) in power bi.
I have a measure which is basically counting distinct values in a table (table 1). From this column I want to multiply the distinct ...

**0**

votes

**1**answer

27 views

### Oracle SQL join local table

I included some data to excel via an oracle connection/ sql.
It is just a typical SELECT-FROM-WHERE SQL, for example:
SELECT
ID9.XDAT
ID9.xIDD
ID9.XSTR
FROM
POOLGEN.OPPPID9 ID9
WHERE
...

**0**

votes

**0**answers

23 views

### Calculating price based on several conditions

I am using Power BI for a billing / summary tool and am a bit stuck on how to proceed.
I have 2 tables, 1 being the price lookup(dimension) and the other is a transactional table (fact).
I need to ...

**0**

votes

**0**answers

12 views

### Display or not display column in Pivot Table in function of the column selector and the granularity

I have a pivot table in Excel with in my columns: Each months if I click on one month I have each weeks of this month and if I click on one of the weeks each days of this week.
My problem is that I ...

**0**

votes

**0**answers

37 views

### Is there a way to export Power Pivot Data Model to Power Query?

I have a power pivot data model of around 20000 rows only and wish to take that data over power query window for further modification. What i'm doing right now is , go to Power Pivot Manage Data Model ...

**1**

vote

**1**answer

61 views

### Power Query Function - Optional Arguments - Token Literal Expected

I am trying to create a function in powerquery that will accept optional arguments. The purpose of the function is to establish a common directory that will be used for text imports in other queries ...

**1**

vote

**1**answer

85 views

### DAX. Problem with subtotals and grand totals

hope you are doing well and can help solve this puzzle in DAX for PowerBI and PowerPivot.
I'm having troubles with my measure in the subtotals and grand totals. My scene is the following:
I have 3 ...

**0**

votes

**0**answers

47 views

### Building Connection Between Power pivot and SAS using VBA

I'm trying to build a connection between Power Pivot and SAS so that I can import my SAS dataset directly to Power Pivot Data Model, using VBA. I'm using the Add2 function and keeps getting the same ...

**0**

votes

**0**answers

21 views

### Imposible to group by day in Excel Power Pivot

I have a Power Pivot in Excel link with a table who come from a connexion with external data.
I would like to have in the columns of my power pivot table the date in week(like each column is a week). ...

**0**

votes

**1**answer

29 views

### Calculating median of differences of columns

I am tracking production of a lot of items. For each item I have timestamps along about 15 registration points in the production process. I want to create a Pivot with statistics of the time it takes ...

**0**

votes

**0**answers

45 views

### How to get inactive customer count accounting for customer creation date?

I have a sales fact table that contains customers ids and is linked to customers dimension table that contains all registered customers in the system with entry/ customer creation dates. Sales fact ...

**0**

votes

**1**answer

40 views

### How to calculate daily population in DAX AND be able to slice it by columns

Updated Problem:
I'm trying to calculate the daily population of patients at our center for any given date using the patient's booking date (start date) and their release date -- using DAX and Power ...

**0**

votes

**0**answers

23 views

### Cannot get multiple powerpivot lookup to work

I have a set of order data and invoiced data. I am trying to match the date from the order data using the following formula but it is returning the same value to every row.
=CALCULATE(FIRSTDATE('...

**0**

votes

**1**answer

41 views

### How to calculate daily population in DAX

I'm trying to calculate the daily population of patients at our center for any given date using the patient's booking date (start date) and their release date -- using DAX and Power Pivot. I'm ...

**1**

vote

**0**answers

23 views

### Resolve “missing relationships” message

I get the "relationships between tables may be needed" message in Excel 2016 PowerPivot. I suspect this is because I've got a many-to-many relationship, but lack experience to be sure. I'll be happy ...

**0**

votes

**0**answers

56 views

### EXCEL PIVOT TABLE - Independent rows (without hierarchy)

In Excel 365, I have a pivot from my Power Pivot data model, that regroups a customer & transaction table, linked by customer-id, and a calendar table.
On my columns I added different Time slots ...

**0**

votes

**0**answers

39 views

### Export Excel PowerPivot recordset as offline cube

I have a PowerPivot model that has Power Query as a source. Is it possible to create an offline cube from this?
I've tried creating a pivot table out of the PP and then using the pivotttable....

**0**

votes

**3**answers

69 views

### Identify whether a record has many products

I am using both Access and Power BI to solve this but to no avail.
In principle it actually is quite simple but I need to do it in a particular way for subsequent pricing calculations.
Essentially, ...

**0**

votes

**1**answer

26 views

### Run-time error 1004 when trying to select OLAP pivotfield array element

I'm having trouble looping through the items in my OLAP pivot field. I have an array which contains the names/items I want the filter to loop through, but I cannot get the pivot field to select an ...

**1**

vote

**1**answer

48 views

### Filter a measure in Power Pivot

I'm trying to group the far right column based on the (top 5/ all the rest) of the rank column. I need a result where I can display a chart that shows the aggregated 'number of sales' for the (top 5/...

**1**

vote

**1**answer

48 views

### DAX calculate previous value also when it doesn't exist

I have data that collects "snapshots" of a Warehouse.
It has three columns:
SnapshotDate, Item, OnHand
(Real data has nearly 100.000 records for each SnapshotDate, here I post an example)
I want to ...

**0**

votes

**3**answers

46 views

### Powerpivot diagram view keeps showing blank screen?

I wasn't exactly sure, whether to post this on superuser, given this is likely a visual bug,
but who knows, maybe I'm just doing something incorrectly given I'm
new to powerpivot
I've recently ...

**0**

votes

**1**answer

99 views

### Problems with dax summarize and Scalar error

I get "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." when i try to summurize "Measure" based on column and expression
I have written the code ...

**0**

votes

**2**answers

39 views

### Calculate median of aggregated values for a category using pivot table

I have hourly data about sales $ of certain categories (Fruits, snacks etc). I'd like to display the median of daily sales values over a month or an year using the hourly data in the table. So, for ...

**0**

votes

**1**answer

31 views

### Tables Are Not Linking. Returning ALL results?

on the file I have (link here: https://drive.google.com/open?id=1Cwdli7HG9npIUPg9V_y6v3Brro7vT_9A) I am really struggling trying to link both my tables by "Booking Ref".
It's clearly the unique ...

**0**

votes

**1**answer

43 views

### Calculate measure involving dates on a factTable with DAX

I have this problem:
given a "Movements" factTable that holds a list of warehouse transactions.
I want to know how many items arrived, how many were shipped (and this is trivial) but also how many ...

**0**

votes

**1**answer

43 views

### Dynamically refreshing CUBEVALUE

Trying to dynamically refresh a bunch of CUBEVALUE functions to build a report. One of the dimensions of CUBEVALUE refers to an Excel validation list.
What I am trying to do is loop (via VBA) over ...