Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months
Jun 9, 2015
I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following
Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.
I tried the the DATEADD function instead but got no luck and data came out wrong!
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
I have a calculated measure that is "Cost Per Patient:=sum([ActualCost]) / sum(Patients[PatientCount])" and it works fine. I'd like to be able to create another calculated measure based on "cost per patient" that uses min, max and average of "cost per patient" but dax wont let me.
I've read about dax calculate but not sure how to use it in my situation. Here is background on the problem:
There are 2 fact tables, prescription cost and patient count. Both are related by a practice dimension. The practice dimension has a geography hierarchy of county - town - practice.
The "Cost Per Patient" works fine with the geography hierarchy. By that I mean it shows the correct value when looking at the different levels. For my new calculation, for example looking at the list of counties, I want to use the min, max and average of "Cost Per Patient" of all counties. To break the problem down, I was going to start with creating and testing separate calculated measures for min, max and average and that's where I am stuck.
My understanding of the problem is it's something to do with the context of where the calculation is being made. As I want to perform the min, max and average on all the children of the parent. This article explains it but I can't figure out how to apply it to my situation [URL] ....
I've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?
I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
I have some data in Excel - sheet1 which would be static.
Now I need to import data from SQL Server (using a query) and Union above static data with this SQL data and later I will have to create PowerPivot table in Sheet2.
Which is suitable option for me to import data from SQL to excel as I see "From SQL Server" option under "Data" and "Power Query" tab as well.
How to merge above SQL data with existing static data?
I don't know if the question has been nailed down. Aside from deleting tables, can we delete the *content* of data within the tables. It doesn't seem crazy that, if you can pull in data from a feed then you should be able to remove the content out again (without also destroying the user's meta-data work ). Reasons for this include:
- Security (a user may not have rights to see *my* data and should go refresh their own) - Size (workbook doesn't need to have GB's of irrelevant data saved to disk in a workbook if it was just useful during development phase to a pre-production data feed) - Bad data (pre-production data feed is not good data) - User-friendliness (data feed was refreshed 2 years ago and workbook was saved to file server. Users shouldn't be presented with irrelevant data, but should get empty pivot tables until they go do their refresh)
Obviously Excel internally knows how to clear out PowerPivot data, given the prompt shown here: [URL] ....
But how does a user initiate this on their own (corruption aside)?
Previous time this question was asked, without a real resolution: [URL] ....
I have a table in `PowerPivot` which contains the logged data of a traffic control camera mounted on a road. This table is filled the velocity and the number of vehicles that pass this camera during a specific time(e.g. 14:10 - 15:25). Now I want to know that how can I get the average velocity of cars for an specific hour and list them in a separate table with 24 rows(hour 0 - 23) where the second column of each row is the weighted average velocity of that hour?
In a separate `PowerPivot` table I have 24 rows and 2 columns(column1 is for hours and column 2 for weighted averages) and when I enter my formula, the whole rows get updated with the same number. My formula is:
This is an SPC chart controlled by a slicer that operates a powerpivot table. This is then copied (by cell formula) into a "normal" table where the average, UCL, LCL and Erlang are calculated which are just basic calculations involving average and standard deviation. To make it work the values in the Average, UCL,LCL and Erlang must be repeated all the way down the table to create the chart. In the current format it works well and using a standard table keeps the chart range dynamic.
However this is a very clunky solution involving repeating the data tables in excel. I need to create dozens of charts and it will get large and slow.I would like to create the whole thing in a powerpivot table using measures so i can use powerpivot charts and ultimately powerBI. The data column PasID is a text column so I need a measure that calculates the "count of PasID" for each day(the row labels) and then repeats that value down a whole column in the same way the standard table does. I couldn't figure out how to get the correct number repeat down the whole column, which measures to use,Whether to create calculated columns in the data model or any of it. SO I need to be able to get a count of a text column then display the average of that count in a second column all the way down.
I've been working with powerpivot and I'm trying to compare 2 text values that are in 2 different tables.For example in TableA I have the column Value and I have the values G and C. What I'm trying to do in TableB is compare the values from TableB with TableA.BOTH values are text but I keep getting an error
=IF(TableB[Value]=TableA[Value], 1,0)
For some reason the TableB doesn't recognize TableB[Value] as text...
The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g
1/1/2000 1/2/2000 1/3/2000 etc..
I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.
I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
I have a pivot table that connects to our data warehouse via a PowerPivot connection. The data contains a bunch of comment fields that are each between 250 and 500 characters. I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
I am connecting to an Access data source which is password protected. But when I set up the connection in PowerPivot what User name do I need to use for the connection. I have tried various log on user names but non have worked.
I have a formula that should use data from 2 slicers:
Spend per period (changing currencies & dates):=[Spend per period]*CALCULATE([Sum of Value],FILTER(Currency,Currency[Date]=[End Date]),FILTER(Currency,Currency[Attribute]=CurrencySlicer[Attribute]))
I managed to link the [End Date] from the slicer to the formula, however the [Attribute] field is not numeric so I can't duplicate the same methodology.
{FYI: End Date:=LASTDATE('Finish Date Slicer'[Column1]) }
I assume that I need to build a formula to extract the data chosen in the slicer, and can't connect it directly to the slicer.
I have a powerpivot table which groups customers into ranges of sales figures. When I use a slicer to slice by region, I lose some of the ranges because they contain no data. I need them to show up even if there is no data. I've checked all of the boxes to "Show items with no data on columns (and Rows). But this has no effect. I created a measure using the following formula which also has no effect.
My PowerPivot report is using quite large data that can be split by subsidiary. I’d like to let user select subsidiary. The selection would trigger change of the PowerPivot’s data connection so that the SQL would change into “SELECT
* FROM dbo.ECSPURSL00 WHERE Subsdiary = 'selected subsidiary'”.
The Excel 2013 has Model class that should manage the PowerPivot data sources. The object explorer shows CreateModelWorkbookConnection method, but I can't figure out the VBA syntax.
I have a DATESMTD function which is not working. This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero. See below my formula:
MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])
Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.
So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date for Sept instead of zero. What can I do to make the system insert zero in the month to date column instead of the $200.00. What am I doing wrong in the formula.
I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.
Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -
UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations) FROM tblColumnNames
I have an Excel 2013 file with lots of DAX connected to an Azure database. I'd like to reuse all that work by changing the data source for the PowerPivot model to a different database which is an exact copy (just empty) on the same server, but Excel won't let me. In PowerPivot I can change the database connection, the user ID and password as well as the connections name. When opening each table properties (inside PowerPivot model) the new connection is used and all old data is removed, but as soon as I refresh using Existing connections, both from PowerPivot or from the Excel Data tab, the old connection is used and old data is reloaded.
If I use Existing connections from inside PowerPivot, I can se that the new connection is highlighted and has the correct variable, but I think maybe that one is run first, then the old one is run afterwards (or something like that).
On the Excel Data tab, I can see that the old connection is the only one Excel itself seems to know about, but I cannot change anything there as it's read-only.
There must be a way to change this. Even with copy and paste it would take me days to recreate this Excel file from scratch and it would be a serious flaw and reduction of usability for PowerPivot.
I am trying to import the data from SharePoint into my PowerPivot window as a Datafeed. I am able to successfully import the whole data from SharePoint list. But, now i would like to apply a filter (Where Clause) before importing the data.
Purpose - relate Server to Software, and Server to App and display it in power view.Current State - I am able to display that Server/Software and Server/App combos separately in power view. However when I add Server/Software/App it throws the warning as seen below. This leads me to believe the relationships aren't transitive so to speak.
Supporting Info - v_gs_installedsoftware is the software table. It relates to v_r_system which is the server table through resource id. The software table can have many rows with the same resource id, however the server table only has unique resourceids (think of it as a server can have many software products installed). Table1 is the application table, it relates to the v_r_system table through the resourceid. Each server from the server table can have many applications that it actually plays a role in (think of a web server that hosts multiple websites for different business units).
I have an Excel database file that contains the total passenger passes from a specific location. The total number of passenger passes is counted in a period of 2 minutes(e.g. 14:45:00 to 14:46:59). I have imported my database into PowerPivot and have also created relevant PivotTables and PivotCharts with some slicers to analyze them. How can I create a slicer which filters data in greater periods of time like hour, day or month?
I need to create a data model for powerview report but I don't have data right now. The data will be loaded later after I create the report. What technique should I use??
I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever.
Trying to modify the workbook connection string but it is greyed out and unable to change the Provider= from SQLNCL10 to SQLOLEDB. I am able to change the PowerPivot Data Connections connection string but not the workbook connection string. On the forum only see where people have asked the question but it seems like the people who've asked ended up recreating the data models.
I have uploaded excel report with data model , but when i click on ,anage data refresh link it is showing shedular disable. I have check logs then I am getting analytics server connection time out error.
Right now architecture of system having different analytics server and sharepoint server is also different. Any better solution due to which i can able to schedule my reports.
One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections is still taking the old connection string of old server and there is no option of changing workbook connection string .
I am able to edit the powerpivot connection but workbook connections are not getting updated they are still taking old server connection string.
I am using excel 2010 and SQL server as a data source. When I create a report with the User name "demo_user" which has db_writer access, and mail it to the colleague , he could not refresh the data with the "demo_user" credentials and apparently its throwing user name or password invalid. The one who created only could be able to refresh the data.
What should I do If other people want to refresh the data? How could I fix this issue? We are using SQL server 2012 and excel 2010 powerpivot.
