power pivot

Hi, I have an excel file that is 80mb in size. I have daily files for several years that I upload each day. I would like to see if I could save time by storing prior years already. They are csv file format 365 files x 4 years and take 30 minutes to upload each day. Each morning I need to open...

I have a table that shows orders. I have an order number, and open date, and and end date. This has been loaded to the data model as I use Power Pivot (and this table is linked to others.) Within the data model I have a calculated column that calculates the number of days between the open and...

I use a CUBESET formula combined with CUBERANKEDMEMBER to generate a list of, say, brands with several filters which users can select from cells in the excel worksheet. One of these in the CUBESET is a date range. I also want to use this date range in the sort_by argument part of the CUBESET...

Hello, I have created pivot tables (including measures from Power Pivot) and added some slicers, but for some reason one of them still keeps returning all of the possible results and just shows 100%. Any ideas why? See screenshot below:

Hello there, I am new working with Excel data models, and I would like to know if is possible to do the following: I have a table that lists elements by boxes. Box type 1 contains elements 1,2,3,4: box type 2 contains elements 2,5,7,9; Box type 3 contains elements 2,4,6: Box type n contains...

Hello, I've come across an annoying "feature" while trying to create an Excel based report. Basically, I've created a data model using Power Pivot and added a couple simple measures - Forecast Accuracy (FA) & BIAS (see screenshot). After adding them to pivot tables everything seems to be...

Hi all, I have a measure if statement to calculate the AAFR based on the different companies for YTD. When I set the filter to 'Maine Group' it calculates the correct value, when I select 'Smyth Steel' it calculates the correct value however if I don't filter the data I get a value approx. 10x...

Hi all, I have 5 KPIs created in measures that I want to get a total value for, I've google it all and the best option i can find is : =[Accident KPI Score] + [Site Inspection KPI Score] + [Site Returns KPI Score] + [Operational RA KPI Score] + [NC Target Date Met KPI Score] however this...

HI, recently could not get the relationship form date to fact table in the right direction , i had several tables with date columns and had created a calendar, but the direction was always wrong, so I then tried joining to one of the other tables this worked fine, so I deleted from data...

Hello, I have a Power Pivot table (see below) that shows the emails reply rate (number treated/number received) for the employees of a company, If they replied to at least 80 % of the emails. the target is reached (YES). I have created a calculated column that show for each person, service and...

Hi, I am trying to convert a report with BOM line data that shows the parent assembly in column A and the child component in column F (see below screenshot). All the data for the child component is contained in each row, with the parent assembly listed for each BOM line. However, the child...

i am using Microsoft office 2013. and unable to find power pivot tab in excel. this tab is also not available in COM add-ins. then what can i do? from where can I do the same.

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

I have a Power Pivot table. In the Values, I have a measure that calculates a percentage. In the Rows, I have two fields and in the Columns, I have one fields. This all produces the values wanted. Next, I need one more column that shows the average of those values in each row. The problem: I...

I have worked with Power Query before and thought there was a way to denormalize a table. The original data is State and different ethnic groups as different columns with the rows with population for each ethnic group. I would like the output to be three columns State Ethnic group...

Hi Guys, Pretty new to excel so hoping there's a simple answer to this that I'm not aware of. I work in marketing insight and part of my role is selecting the best customers to send each of our direct mail campaigns to, and I want to create a workbook in where I can walk through our monthly...

Hi, I'm trying to create a DAX formula to figure out the current QUARTER. Below are a few of my attempts. Our year starts Feb 1 to Jan 31. =if(dCalendar[DATES]<today(),if(ROUNDUP(MONTH(TODAY())/3, 0)=dCalendar[QTR],"CURRENT QTD",""),"") QTR =if(ROUNDUP((MONTH(TODAY())-1)/3, 0)=0,4) So...

Hi, I have an issue that when I'm working with many slicers I have to wait each time I make changes to my slicers before I can go to the next slicer to make my selections. I wonder if there is a way to work with all slicers before the pivot table refreshes. Thanks

Hi, I am trying to create a power pivot measure that sums with two filters and have tried a variety of ways and it will not work for some reason. There is something simple or obvious that I am missing and guidance would be appreciated. Example attempts below This code works CALCULATE(SUM('09...

Hello Everyone, I´m having an issue with a pivot table from the data model. I want the quarterly subtotals to show monthly average rather than sum, but the option is grayed out: Is there a way I can work around this? Thank you!