powerpivot

  1. P

    DAX PIVOT: Projects Closure Date Field - Number of Days Change - between differing Forecast Versions

    Hi, I'm new to DAX, but I'm sure the solution here should be relatively easy? Any elegant solution gratefully received. I need to be able to report where, between 2 Forecast versions, the Closure date for a Project has slipped (or accelerated). I've used 'Calculated Items' within an ordinary...
  2. H

    Drill through - Behaviour in PowerPivot/Pivot Table

    Dear All, I regularly use Power Pivot and Pivot tables for various analysis and drill through the figures to see what combination of transactions makes those figures. In my raw data there are 44 columns. Every time I drill through, I get an exploded view of that table showing raw data - all 44...
  3. V

    Cube Value error when using specific column

    Hi I need help trying to understand an error when using below cube value formula, I have a column of "system" which when using in the formula will make everything error however if I was to use another column to slice by everything works fine, in order to solve this could you give me some reasons...
  4. M

    Measure for IF not working

    Hi all, I'm trying to write a DAX for an IF function where there are multiple criteria and true answers but it keeps getting stuck and generating on 1 answer for all outcomes any help correcting it would be appreciated. NB all my tables are linked in data relationships. Below is what I have so...
  5. P

    PowePivot - Create a slicer of a measure or calculate values from one table using filter from another table

    Hello, I've been struggling quiet a lot with this issue and been looking high and low but I'm not able to find any answer. Hopefully some master can help me solve this issue! I have two table, lets call them Table1 and Table 2 in Power Pivot. They have relation to each other through Column...
  6. M

    Calculate a rolling average using measures in power pivot

    Hi all, I've been trying to calculate the rolling average of 'employee count' to use in within another calculation but the formula I have doesn't appear to take the last 12 months but the entire column average, any help appreciated. Formula: =CALCULATE((sum('Accident Log'[Count...
  7. K

    "Problem Saving PowerPivot document"

    Anybody got any ideas how can I work out how to fix this problem? The error message below is popping up: ============================ Error Message: ============================ Value does not fall within the expected range. ---------------------------- Error occurred while setting the Sandbox...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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...

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
Back
Top