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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
2answers
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
2answers
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
1answer
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
0answers
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
0answers
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
0answers
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
3answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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 ...