1. P

    Copy method of worksheet class failed" with pivot table

    I have a pivot table created in Power Pivot, it's on a sheet ("Sups") that when using VBA to copy and paste will work sometimes and in other instances, it will not, triggering the "Copy method of worksheet class failed" error. The line that triggers the error is below. Any thoughts? Sub...
  2. J

    SPC In PowerPivot

    I'm looking for a better way to calculate control limits for an SPC chart in PowerPivot. I can do it with a small amount of data (~100 rows), however it is very inefficient and slow when doing it on a larger data set =(1000s of rows). The formula to calculate the control limits is: Mean ± Mean...
  3. E

    Help with "OR" in data validation

    I am running Excel 365. I have two named tables, one is called "Inquiry" and the second is called "JO." This is a customer service type database, where first we get an Inquiry, which we log in the Inquiry table in the column named "Inquiry Number." At some point, a decision is made on what to...
  4. K

    Power Pivot: get value for Previous Non-Consecutive Date

    Hi Guys, I have an issue which seems to be easy but it's DAX so it isn't :-) I have similar Table (called "Data") as source: Added Table to data model then inserted a Power Pivot: count values in column "C" per Date and per Country: I started to get proper values for previous date but...
  5. C

    Power Pivot category subtotal % variance not calculating correctly.

    I am trying to build a pivot table using the power pivot and the data model built from two different files. With this pivot table, I am trying to compare Quarterly 2022 revenue vs. the same period in the prior year. In the data model, I added columns for Q1'22 $ variance, Q1'22 % variance...
  6. A

    Expand 3 Columns with Tables Simultaneously PowerQuery

    Hi all, I am fairly new to PowerQuery. I have 3 columns, each one with a Table (all tables have the same structure and number of rows): When I expand Column 1, every row of the expanded table gets a table for Column 2 and 3, So I get a big number of duplicates. Is there any way I can...
  7. J

    Trying to pivot with Dates across

    Guys, here's my issue.... I have this data grid that I need to pivot it. Simple. The problem is that the dates comes across (toward the right) of my main columns. Usually, I'd rearrange my data in a vertical rather horizontal direction BUT It cannot be moved as per client's request. Well...
  8. M

    Measure needed to return last salesperson to have dealt with a customer

    a task I've been set as part of a PowerPivot creation 1.Enter the last rep to have sold to the customer URN on the query tab of the PowerPivot. Now there's no query tab for me on powerpivot and as far as i'm aware query was part of a ribbon thats now get and transform which has nothing to do...
  9. J

    DateDiff not working the same in excel as it is Powerbi

    Hi, In PowerBI I have a measure with DaysPastDueDate = DATEDIFF (AUDIT_HEADER[DUE_DATE], TODAY (),DAY) That doesnt seem to work in Excel PowerPivot and it requires it to be a calculated column. If I make the column witrh that calculation it doesnt seem to like it when the start date is...
  10. M

    Export data from connection to txt file without loading to tab.

    I have a data connection that contains 3M rows of data. I want to export a subset of that data (total 1.5M rows) to a text file. Can that be natively exported/written to txt file without loading to tab (which obviously is beyond row limitation for a sheet). If it is possible, I would be...
  11. M

    Combining two date cells with measures

    Hi, I'm struggling to get this to work, any solutions would be much appreciated. - data shows forecasted and actual sales for the year. - data has two date cells, 'projected close date' and 'actual close date'. i.e. when we think it's going to land and when it actually landed. - want to create...
  12. H

    Powerpivot: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique"

    Hi, I have a problem getting two tables to work together despite unique relationship. The table 3 has a column (date) which is unique. The Table 1 and 2 have also dates but not unique. When building the relations between these it works fine, but when i excel trying to use these data in a pivot...
  13. H

    DAX to filter values based on other tables

    Hi, I would need advice on how to create pivottable, based on inputs from several tables. 1. First I have an account masterdata table Account Sign Level hierarcy 1010 1 Unit Balance sheet 5610 -1 PG P&L This table defines characteristics of each account, and if the account...
  14. M

    PowerPivot Calculating Measure on semi-aggregated data

    Hi all, This forum has already helped me so much in the past months but now I have the first question I couldn't find an answer to anywhere else, really hope someone can point me into the right direction. I have an Excel model where I use a data model and power pivot. Basically user enter the...
  15. S

    Power Query issue with select dates

    Hi, I would like my POWER QUERY to filter only a select date range. Sales from 730 days ago to 830 days ago. Sales from 365 days ago to 465 days ago. Sales from today to 100 days ago. Here is my current code: SELECT tblsale.sale_link, tblsale.loc_code AS [LOC#]...
  16. GraH

    Excel expert gevraagd met kennis van PowerPivot en PowerView (Brussels Area)

    Beste, Mijn voormalige manager, CEO van Ypto, de IT leverancier van de NMBS, zoekt dringend een excel expert met kennis van PowerPivot en PowerView. Het betreft hier een korte opdracht van ongeveer 1 week. Mogelijks kunnen nieuwe opdrachten volgen. Als je in België woont en interesse zou...
  17. V

    Powerpivot lookup table to two different tables that are themselves connected - best way to model?

    I have three tables. I'd like to work with them using data connections in Powerpivot so I can create simple Pivot tables. The tables are: One is a fact table with the usual customer, product number, sales, gallons, etc. The second is a lookup table which allows me to lookup a "cost center"...
  18. tbablue

    PowerPivot - New measure - calculate based on two criteria

    Hi Forum, Quite straightforward I'm sure - but I'm new to measures in PowerPivot. I have three columns; Definitive Name, Yr.Mnth & Employee FTE. The table name is 'Summary Query'. I need a measure that will calculate Employee FTE based on the number of times that 'Definitive Name' appears in...
  19. B

    Relationship won't work on Power Pivot

    I've attached an image with my problem, of course I did this in separate sheets and only copied it into one so it's easy to visualize. I have in the first table my sales and in the second one every article with its brand. I want to know, in each month, how many different brands were sold, but it...
  20. W

    Power Pivot - aggregate within groups to determine max value

    I'm looking for a formula (for Power Pivot) that aggregates within certain groups and across other groups to determine the maximum. Here's my data table: State Customer Fruit Qty NY A Apple 5 NY A Orange 1 NY A Pear 5 NY B Apple 1 NY B Orange 6 NY C Apple 2 NY C Orange 2 NY C...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back