1,871 questions
0
votes
1
answer
102
views
VBA Code to copy specific columns in a pivot table & paste to another worksheet
I'm updating an excel spreadsheet which has VBA codeing in the background. The changes I have made to a table means the vba code now doesn't work correctly.
The original code is:
Public Sub ...
- 1
1
vote
1
answer
51
views
Data disappearing when CUBEVALUE function is refreshed
I'm connecting to a Power BI semantic model in Excel and using the CUBEVALUE function to retrieve data. Each formula contains around 10–11 member expressions, and everything works as expected when I ...
- 11
0
votes
1
answer
92
views
problem with creating a data model in Excel VBA
I want to create power pivot tables using the distinct count function - which is only available when you "Add this data to the Data Model". It seems I need to create a connection to the ...
- 152
0
votes
0
answers
24
views
How to use Power Pivot connection only data measures slicers in a dashboard
I have to create a interactive table dashboard showing Last 1 Week, Last 4 Week, Last 13 Week, Last 26 Week and Last 52 Week columns for a list of sales products. I need to show 6 different measures ...
0
votes
1
answer
63
views
Subtraction of previous period in PowerQuery
I have 2 tables in power query which I would like to append. But before appending these 2 tables I would like to implement the following subtraction in power query. How to get around with this ? many ...
- 3,031
1
vote
1
answer
70
views
How to create Calculated Measure to find matching values between two tables in Excel Power Pivot
I have two tables (i.e. 1st is 'Sales' Table, 2nd is 'Territory' table) in my Power Pivot, and I have established relationships between the two using a 'territory' column.
The 'Sales' table contains ...
- 23
0
votes
1
answer
99
views
Excel: How to get a column of a related table into my worksheet?
I have created 3 tables in my worksheet: names, roles, costs
names:
ID
NAME
ROLE
1
A
1
2
B
2
roles:
ID
ROLENAME
SALARY
1
X
10
2
Y
50
costs:
ID
NAMEID
WORKEDHOURS
1
1
10
2
2
10
The tables are related ...
- 25
0
votes
2
answers
126
views
Can we use power Pivot to categorize Bill of Material in terms of explosion level
I am in the middle of an assignment and need to make an easy way to "filter" a bill of material through power pivot. Imagine the below is my dataset (simplified)
If I want to have an output ...
- 11
0
votes
0
answers
64
views
How can I unpivot an Excel sheet to create a dashboard?
I have this sheet and I want to make analysis on this sheet like pivot tables to create pivot charts, and when the data is updated every weak this should affect the dashboard also, but when I convert ...
- 11
0
votes
1
answer
73
views
Calculate correct overall summary revenue in DAX
I have a table of data which I need to create pivot reports from, each line of data is all set out as per example below:
The overall estimated revenue is where my problem is.
This is a figure for the ...
- 129
1
vote
3
answers
141
views
Power Query: after combining 2 tables got something strange
I have a column in which each value is repeated twice. And each value has its own share (input data). I need to calculate the average share among the first occurrence of values in the first column and ...
- 35
0
votes
2
answers
267
views
DAX CALCULATE does not respect Pivot Table row context
Excel Power Pivot
Lookup Table = Calendar;
Data Table = Sales
Pivot table Rows: Calendar[Year]
Pivot table Values: [Measure] = CALCULATE(SUM(Sales[Amount]); Calendar[Date] < DATE(2003;01;01))
...
- 3
0
votes
1
answer
36
views
Can Power Query or Power Piviot gives the Sub total of each section?
I have a table with multiple sections, each section has different queues. I have to subtotal at the end of the each section.
How Can I do it in Power Query or Power Pivot? I am not getting any idea to ...
- 491
0
votes
0
answers
49
views
How to calculate long term absence DAX
I have a dataset where I have various activities with the primary two in question is paid hours and absence hours.
We measure long term absence as five days of absence in a row, in which all dates ...
2
votes
1
answer
72
views
Sum of sales of delimiter column
I have a table which contains Item and its Sales.
I need Sum of all items & the column may have delimiter with ",".
How Can I do it in either Power Pivot or Power Query.
Table1
** ...
- 95
0
votes
2
answers
731
views
The pivot chart does not refresh with the 'Refresh All' button
I have an Excel file that contains a table. In the same Excel file, I perform a Power Query on this table and then load it into Power Pivot.
In Power Pivot, I create two measures (NB_KO and NB_OK) and ...
0
votes
1
answer
40
views
Distinct values based on boolean measure
I have a table like
Point of sales Employee Category Answer Supervisor Date
POS35 Emp6 Wine 2 SV3 07.05.2024
POS70 Emp13 Cognac 1 SV1 17.05.2024
POS237 Emp1 Wine 1 ...
0
votes
1
answer
185
views
DAX Measure: check each row in a data subset for a specific value
I try to check for each row if a DAX measure is <0. However, the check shall be done for each ID separately and not for the whole column. If TRUE, all rows related to this specific ID shall use ...
- 1
0
votes
1
answer
156
views
two tables share the same unique ID but with their own set of data. power pivot shows the same sum for each record even after creating relationship
I have two tables. one table has unique values while the other table has records based on those unique values. I added them to a data model and then made a pivot table. my intention was to find sum of ...
0
votes
1
answer
19
views
PowerPivot change Total % depending on date range that's selected
I have a table as per below:
My issue is with the Percentage of Weekly Hours. Obviously this will only be correct if the person using the workbook is selecting one whole week.
I want to know how I ...
- 129
1
vote
1
answer
210
views
Excel to produce "tree" tables
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 ...
- 41
1
vote
1
answer
261
views
PowerPivot Relationships showing blank rows of data and not calculating the measures correctly
I have 3 different data feeds going into PowerPivot as per below:
Quality Scoring Data, Learning Scoring, Data Call Handling Data
To get all three of these to pull through into one pivot table with ...
- 129
0
votes
2
answers
77
views
DAX IF function syntax error- creating a conditional column
I've got a csv with 900,000 rows.
42 of them are duplicates.
PQ's Remove Duplicates function removes rows that aren't true dupes.
I can successfully filter for these 42 rows in the PQ Editor, but I ...
1
vote
1
answer
165
views
PowerPivot Data Model Causing Cartesian Product
Summary
Using a two table PowerPivot data model, I'm not able to create a pivot table that has multiple rows filtered in the same way a single table does using the same columns.
Details
I have a ...
- 478
0
votes
1
answer
72
views
Apply PivotTable filter
I created a table from Power Pivot.
I am trying to apply a filter to the pivottable.
It is constantly coming up with this error:
I tried
ThisWorkbook.Sheets("DATA").PivotTables("...
- 129