Using Excel 2013.
I am trying to get DistinctCount from a field if another field in the same table is greater than zero.
PowerPivot DAX -COUNTIF
[Code] .....
The count returned includes all not just values greater than zero. How can I get just values greater than zero?
Using Excel 2013.
Trying to create a Calculated Column in a table to put Expense in 1 of 4 categories
I came up with =CALCULATE(sum([TotalExpense]),FILTER(factExpense,factExpense[ExpnseType]="Sundries"))
But that is returning total instead of a total for each record What did I miss?
This works for the first Calculated column: =CALCULATE(sum([TotalExpense]),FILTER(dimExpense,dimExpense[Type]="Sundries"))
But as soon as I copy the formula to the next column and update the type to "Wines_Spirits" I get
A circular dependency was detected
Found the answer on SQLBI website I added a unique id to each row and in Table Behavior in the Data Model set the Unique_ID as the Row_Identifier
[URL]
In Excel 2013, I have a pivot table showing the deals that our sales team are trying to close during this Qtr. and how much revenue we hope to get from those deals during this Qtr, next Qtr and beyond. The FILTER area of the pivot table is used to select the current Qtr.In the ROWS section, I set the "Deal ID" field with a value filter to Top 10 items by current Qtr revenue.
Data is refreshed weekly.
At the start of the Qtr, this works perfectly.
Towards the end of the Qtr, (when there are less than 10 deals with revenue this Qtr, and lots with 0), the "Top
10" filter is showing all the deals with 0.
How can I filter a pivot to show items that are >0 AND Top 10?
How to get Power-query and Power-pivot for Excel 2010?
View 9 Replies View RelatedI have some nominal data that I'd like to get into a pivot table (Excel 2013). For simplicity let's say it's a one-question survey with 6 respondents:
Q1: Dogs are better than Cats
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Responses:
Agree
Strongly Agree
Strongly Agree
Strongly Agree
Agree
Strongly Agree
I can create a pivot table with this data and get the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Grand Total
6
This all works nicely, however I require that the other options ("Neither Agree or Disagree", "Disagree", and "Strongly Disagree") be present, even if their values are 0, like the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Neither Agree or Disagree
0
Disagree
0
Strongly Disagree
0
Grand Total
6
What I tried doing was adding a new column and calling it something like Ratings with the following:
Ratings
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Then I set the Ratings column in the "Rows" section of the pivot table and the Count of Q1 column in the Values section. This is what happened:
Row Labels
Count of Q1
Strongly Agree
1
Agree
1
Neither Agree or Disagree
1
Disagree
1
Strongly Disagree
1
Grand Total
6
I have a pivot table like the one below.
What I would like to do is filter the drill down keeping the total of the products (in bold) and showing just one of the name (just ENTA for Example).
Basically I would like to add a filter that Hide some of the data keeping the row total.
I'm Using Excel 2013.
Products
Sell out 4 weeks
Stock Units
Avg 4 weeks
Wks of stock
3160-24PC-AP12
1
[code].....
Using Excel 2013,
I clicked on a field in my RowLabels
I then clicked on the Filter Arrow for the RowField
The SelectedField prompted with the correct field of 4 possible fields
However the item I am looking for is not in the list but I can plainly see it on the screen.
I have several competitors balance sheets (around 15), they all have the same structure, what i will like to do is summarize all this data into a pivot table to have a grand total but also be able to filter the data by single competitor.
I have tried to do multiple consolidation ranges, power pivot, pivot but i was unsuccessful, maybe i arranged the data wrong or im not using the right solution. im using excel 2013
I have a Pivot table showing costs of several types of items.
The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).
So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.
Can this be done in Excel 2010?
I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.
I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
I have a table of data which I am analysing in a Pivot Table. For the majority of the data, the Pivot works very well: however I have a small issue, but it subsequently means the Pivot is useless.
Within the table array that I am referring to, there is a column of data of "Days per employee for a given period". The rows of data within the table array relate to every absence entry per employee, but this final column of data always contains the same figure (although can differ from employee to employee).
When I put the data into the Pivot, I can summarise the absence(s) as a simple sum. However, this final column of data should not be summed, since it is already the sum figure.
This figure though needs to be part of the Pivot, since I need to report on the percentage of absence days per type over the given period. So, the simple representation of =absence day(s)/worked days does not work... well I cannot get it to work. Additionally I have tried features like % of, but nothing.
I am using excel 2007.
I have:
Excel 2003ABCD1StockDatePriceReturn2MSFT31-Dec-1027.91-6.97%3MSFT31-Dec-093054.32%4MSFT31-
Dec-0819.44-45.39%5MSFT31-Dec-0735.619.22%6MSFT29-Dec-0629.8614.19%7MSFT30-Dec-0526.15-2.13%8MSFT31-
Dec-0426.72-2.37%9MSFT31-Dec-0327.37111.84%10MSFT31-Dec-0212.92-21.98%11MSFT31-Dec-0116.5652.77%12MSFT29-
[Code] .......
and would like:
Excel 2003ABCD1Sum of ReturnStock2DateBAINTCMSFT331-Dec-970.83851.14150.5659431-Dec-98-0.33350.68793.2921531-
Dec-990.27041.77732.3668629-Dec-000.59270.4606-0.6286731-Dec-01-0.41240.04620.5277831-
Dec-02-0.1493-0.5049-0.2198931-Dec-030.27741.05841.11841031-Dec-040.2285-0.2702-0.02371130-
[Code] ...........
Code:
Sub codedPTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Data")
[Code] ..........
Only prints this:
Excel 2003ABCDE1Sum of ReturnStock2BAINTCMSFTGrand Total3Total1.99894.69867.331214.0287Sheet2
If you have a more compact macro than what I tried to change above even better (the data is in the simplest testing format).
I have a Pivot Table with pivot fields and data. I would like, through VBA, to get the list of values that can be chosen from a given pivot field.
For example, a list would be Product1 / Product2 / Product3 .... I would like to read that list and put it into a drop down list in a form.
GOAL: I have several Pivot tables on the same sheet with similar fields and I want through macro to allow the user to update them all with one click.
Excel 2007
No empty rows
No empty cells
No calculations in cells
Field names look fine - no punctuation no merged cells
If I select one column - any single column I can generate a pivot table but not with multiple columns
I have a set of sales data and need to create a run rate which is simply = Total Sales/Selling Day
Selling day changes most days.
I put the calculation into my pivot data but it's summing up, (instead on 12 I get 720) so I changed this to Average so I get the right figure in the Pivot but when I then use this field, it doesn't use the Average amount, it uses the summed figure.
So what I need is Total Sales/X
X = Cell Reference
I'm having an issue getting the correct results from a calculated field in a pivot table in Excel 2007.
In column A I have "Business Name", in B I have a sum of the amount of lines a customer has, and C is a MAX of the number of employees the customer has at their location.
In one example I have a business with sum 50 lines and max 30 employees but when I try to make the calculated field 50/30 (should equal 166%) i get 9.2% instead.
The reason appears to be because there are 18 types of lines the customer has and 18 x max 30 = 540 and 50/540 is 9.2%. In the attached example it's all the same business location so the total number of employees (30) is the same for each row and each row in the raw data is a set of lines with similar features.
I attached an example. CalcFieldProblem.xlsx
I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.
I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.
Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
I want to create a report using power pivot while creating the relationship between the linked tables, power pivot is throwing error "The relationship cannot be created because each cilumn contains duplicate values. Select at least one column that contains only values"
View 1 Replies View RelatedI have data sheet that has a field minutes. I a pivot table I have the Average Minutes per category. How it the pivot table can I show the Average minutes as Days, Hrs, Minutes? I am working in Excel 2007.
View 1 Replies View RelatedAny way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)
I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View Relatedvba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
1. I am trying to record a macro where I select a Pivot Table. But in the recorded macro "Pivot Table name" is not recorded neither the Pivot Field Property only the Range name is recorded. But on other systems (workstations) these details get recorded. Does this have something to do with excel settings?
2. I uploaded a macro enabled excel file on my company's "sharepoint" the drop down boxes present in the file get populated via a macro in "ThisWorkbook" page but sometimes these drop down boxes don't show any values. What can be the reason for this? Can't share the file because of data security policy of my organization.
I've two columns in an excel sheet... In column A, I have years & in column B, I've amounts... Now I have made pivot table for the same to have an exact picture of this table... Now, I need a formula applying on pivot table that may identify the amounts in specific years great that 50,000 then great that 50,001 to 100,000 then 100,001 to 150,000....
View 1 Replies View RelatedIn my pivot table I have numerical values for each Agreement ID. The range is big and I need to sum up all the values that are smaller than 75K. My current formula works only in a given cell but when I drag it down the cell reference does not change.
View 2 Replies View RelatedIs it possible to just show numbers greater than an amount in a pivot table?
View 3 Replies View Relatedwhy the Group Field option is not available in my Pivot Table. Other Pivot Tables in the same document, using data formatted the same way will let me format the data (the original data is in MM/DD/YY [h]:mm format). I have floored my data so that I can group by hour of the day, but it's not letting me do that.
View 3 Replies View RelatedI have two fields in pivot table, "HATA TOPLAMI" and "SIRA NO". "Hata toplamı" is sum of items, and "Sıra no" is count of items. İ want to divide these two fields and add it to the pivot table, like "HATA TOPLAMI" / "SIRA NO". I have tried calculated field function from pivot table. İ wrote "="HATA TOPLAMI" / "SIRA NO"". But the summary is false. I want to divide Sum of "Hata toplamı" and count of "Sıra no" but instead it divides with sum of "sıra no". Is there a way to do this?
View 3 Replies View Related