Power Pivot :: One Slicer To Control Two Pivot Tables That Have Different Source Data And Common Key
Jul 8, 2015
I have two data tables:
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.
View 5 Replies
ADVERTISEMENT
Oct 9, 2015
How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?
I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?
<= 0
1 - 6
7 - 12
13 - 18
19 - 24
25 - 30
31 - 36
37 - 42
43 - 48
49 - 54
55 - 60
61 - 66
67 - 72
73 - 78
79 - 84
85 - 90
91 - 96
97 - 102
> 102
View 8 Replies
View Related
Aug 18, 2015
I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example, my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.
The solution I have come to so far involves creating a table with a single column that contains all the single values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.
A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric. The example is available here.
View 2 Replies
View Related
Jul 8, 2015
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 ...)
View 2 Replies
View Related
Sep 23, 2015
We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?
View 5 Replies
View Related
Aug 17, 2015
My actual data is a bit more complicated, and uses fact and dimension tables, but I simplified it here.
I'm trying to build a chart that will compare sales of a single store to regional and national sales. The measures look like this:
Sales = SUM(FactTable[Net Sales])
Regional Sales = CALCULATE([Sales], ALL(FactTable[Store Number]))
National Sales = CALCULATE([Sales], ALL(FactTable))
And it ends up looking like this:
Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:
I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.
View 2 Replies
View Related
Oct 13, 2015
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
View 3 Replies
View Related
Feb 25, 2014
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.
View 10 Replies
View Related
Oct 15, 2015
Is it possible to include a currency symbol in an amount-field in PowerPivot/Pivottable based on a Currency column in a table? Something as the same as with SSAS MD. And I don't want fixed values in my code.
View 3 Replies
View Related
Oct 12, 2015
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?
View 6 Replies
View Related
Jun 11, 2015
When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
View 5 Replies
View Related
Apr 29, 2015
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?
View 2 Replies
View Related
Oct 14, 2015
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
View 13 Replies
View Related
Aug 17, 2015
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies
View Related
Mar 11, 2015
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values.
2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))
Here is the formula for the percent:
=(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
View 24 Replies
View Related
Sep 18, 2015
I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
View 3 Replies
View Related
Nov 23, 2015
I have a data table that contains budget and actual data by month. I use the data to create a pivot that shows actual results next to budgeted results. I need a column that shows that variance between those columns. I think my issue is that the "Type" field contains actual and Budget. I sum on "Type". I can't seem to create a sum since those items are in the same field or am I missing something?
Table design
Month|Division|Subdivision|Type|Dept|Rate|Units|Amount
October|DC|Day|Budget|125|10.00|100|1000
October|DC|Day|Actual|125|10.00|110|1100
Output Design
DC
DAY
Actual
Budget
125 AvgOfRate
AvgOfRate
SumOfUnits
SumOfUnits
SumOfAmt
SumOfAmt
View 4 Replies
View Related
Apr 13, 2015
I am using excel 2010 and creating pivot table from Power Pivot. I created a pivot table with department slicers. All is good, the problem I am having is whilst in an unfiltered position (ALL) of the slicers (departments) I get 200 pages, now when I click on a given department with say 10 pages, I still get the same 200 pages with the first 10 pages showing the data from the clicked department and 190 blank pages.
All I want is to get a WYSIWYG (What you see is what you get) of what is on the screen as my print, but I am getting extra blank pages right after the data. How do I resolve this.
Below are the steps I go thru to print
1. Select slicers in unfiltered position (ALL)
2. Select entire pivot table
3. Select Page layout and select print area.
4. Save
5. Click on Print Preview to preview the print
6. Click on a given department in the slicer and repeat item 5, but this gives me blank pages after the data.
Do I need any other step?
View 2 Replies
View Related
Nov 4, 2015
I work in a medical facility and and client confidentiality prevents loading my pivot tables to sharepoint at this time.
I am creating several reports that combine data from SQL Server, Cerner medical report DB and some lookup tables in Excel.
Everything works great on my desktop but I'm having trouble sharing my work.
Our normal routine is to drop a copy of our excel files in a Pass through file on the server that has strict access controls.
I think my main problem is my supervisor doesn't have powerpivot on his machine. that will be corrected tomorrow.
My question is: When close your powerpivot workbook, do all the connections go with it. If I just drop myproject.xlsx into the pass through will all the links to the varying data sources still be available?
View 2 Replies
View Related
Jul 13, 2015
I have 2 tables below:
Table 1:
Product No Quantity
A 1
B 2
C 3
Table 2:
Product No Grade Quantity
A Good
A Normal
A Bad
B Good
B Bad
C Good
C Normal
C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity
A Good 1
A Normal 0
A Bad 0
B Good 2
B Bad 0
C Good 3
C Normal 0
C Bad 0
View 8 Replies
View Related
Sep 8, 2015
I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.
Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:
->Employee Table: Employee_name, Previous_Manager, Current_Manager
->Manager Table: Manager_Name
Because I have 2 manager fields in the employee table, I need to create 2 links between the employee and manager tables:
-> Link 1: Previous_Manager ---- Manager_Name
-> Link 2: Current_Manager ---- Manager_Name
Right now, one of the links is inactive...
Is there a way in PowerPivot to create 2 active links like that ?
I have Power Pivot version 11.0.3000.0 on Excel 2010 on Windows 7
View 5 Replies
View Related
Aug 2, 2015
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
View 9 Replies
View Related
Aug 19, 2015
I am trying to implement some powerview report in excel 2013 itself (not in sharepoint).
I have implemented a SSAS tabular model and deployed to server. Now my aim is to load this tables in the SSAS tabular database into powerpivot so that i can use this data for creating powerview reports. However when i tried to import data from SSAS, it asks database and MDX query. My requirement is to load the dimension and fact tables to power pivot as it is.
View 7 Replies
View Related
Nov 7, 2014
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
=CALCULATE(COUNTROWS(incident),
apparatus[Incident Response Time] >-1 ||
apparatus[Incident Response Time] <320,
uv_901APP_TYPE[Description]="Engine",
uv_901INCIDENT[Top_Category]="Fire"
[code]....
View 13 Replies
View Related
Jun 8, 2015
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.
View 2 Replies
View Related
May 13, 2015
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.
View 2 Replies
View Related
Jul 22, 2015
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.
NeverBlankAcct:=IF(ISBLANK(Count([CACCT])),0,Count([CACCT]))
View 13 Replies
View Related
Mar 4, 2014
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.
View 5 Replies
View Related
Oct 20, 2015
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.
View 8 Replies
View Related
Oct 19, 2015
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
Month Count
July
August
September
October.
View 3 Replies
View Related
Sep 1, 2015
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
View 2 Replies
View Related
Oct 1, 2015
I have a problem copying data from column A to column B in Power Pivot.
View 3 Replies
View Related
Mar 20, 2015
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.
View 2 Replies
View Related