1,889 questions
0
votes
0
answers
14
views
Query referenced a calculated column which does not hold any data because there's an error in it's expression
I'm getting an the followin error message whenever I try to view/drill down the makings of particular values in a power pivot in excel 2016:
the querry did not run or the data model could not be ...
0
votes
1
answer
36
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 ...
- 2,849
0
votes
1
answer
33
views
How to calculate the average unique occurrences of an ID for day of the week across months in PowerPivot?
Edit. I would like to have average unique visits e.g. average all Mondays, all Tuesdays all Wednesdays in a given month. If there are 4 Tuesdays, I would like to see an average value of visit on ...
- 704
1
vote
1
answer
39
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
1
vote
1
answer
16
views
Events In Progression Problem Excel Power Pivot/Data Model DAX question
I am working in Excel with power pivot.
I have 2 tables: a calendar table and a data table.
The calendar table has 3 relationships with the data table.
In the data table, there are 3 date fields: Case ...
- 11
0
votes
1
answer
56
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 ...
- 21
0
votes
1
answer
18
views
How to rank a totaledd column with Dax as a calculated Column
I have a dataset that has Grocery store name, department name, Employee name... I am trying to Count the number of employees by department to get a total number of employees by department and then ...
0
votes
0
answers
23
views
Adding total rows, calculated rows to sets and Pivot tables
I have two pivot tables - here is pivot table 1
Monthly Transfers by Item
Sum of 10-24 Item #
Sum of 9-24 Item #
Item 1
94
113
Item 2
17
22
Item 3
1
Item 4
1
Item 5
2
3
Item 6
1
1
Item 7
2
3
...
- 481
0
votes
0
answers
22
views
measure subtotal in Power Pivot
I have created a PowerPivot table for employees to check how many evening or morning shifts they had per month
created table
The Deviation is a measure if the % of evening shifts is lover than 25 or ...
0
votes
2
answers
86
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
69
views
Power Pivot Error - contains the blank value and that is not allowed for columns on the one side of a many-to-one relationship
I’m learning the Power Pivot and have encountered an issue while creating relationships between tables. I used already available data to practice and am now facing an error when attempting to ...
0
votes
0
answers
37
views
Combining tangentially related datasets into one table
I've trying to combine two datasets into one easy-to-digest format.
As an example of what my ask is, let's say I run an animal breeding agency.
First, a listing of animal breeders, the name of the ...
1
vote
0
answers
28
views
Using PowerPivot to compute test coverage
For my current project I am using Excel and Pivot Tables quite often. Now, I have seen the nice feature PowerPivot in Excel and I am thinking to use it in my project.
Especially, I want to use ...
- 6,064
0
votes
0
answers
57
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
3
answers
126
views
Is there a way to unpivot data using PowerPivot (not Power Query)
I have a data set that has 50+ columns and it contains monthly sales data. I do not have access to underlying data but I need to be able to manipulate the data. Below is an example of the data set, is ...
- 23
0
votes
1
answer
66
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
0
votes
1
answer
36
views
Calculating a weighted average of existing measures in Power Pivot
I have successfully created two measures (Measure1 and Measure2) and I want to get the weighted average of Measure2- Duration.
Here's how my data looks like and the weighted average I wish to get:
I'...
- 21
1
vote
3
answers
112
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
0
answers
41
views
How to use a selected slicer value to do a calculation on it and use this calculation result to filter a pivot table
I need to use two filters, YEAR and YEAR-1 values, on two distinct pivot tables. The YEAR value is selected from a slicer, for instance, 2024 and then, the first pivot table connected to this slicer ...
0
votes
0
answers
36
views
Why does my YTD calculation based on comparing time to current month not work in a DAX measure?
I have the following data structure:
A calendar table that includes the numerical column [Month] (1,2...12). The latest date in the calendar table is 20 Aug 2024 (or T-1).
A data table that includes ...
- 63
1
vote
2
answers
32
views
PowerPivot to find specific text
I need to find specific text in a column in PowerPivot but the field is sometimes spelt incorrectly.
EG:
Data
Open/Closed
(Cloosed) Emp 1
Emp 2
(Account Closeeed) Emp 3
(CLOSED) Emp 4
(Closeed) ...
- 129
0
votes
2
answers
124
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
1
vote
1
answer
51
views
custom column to show a cell's value before combining files
I am new to Power Query and am trying to combine 30 raw data files into two groups separately. Each group has its column headers and data.
When importing the files into Transform Data page, I need to ...
- 13
-1
votes
1
answer
23
views
Powerpivot with slicer on two tables [closed]
Trying to learn PowerPivot and slicers using two simple tables.
Titles:
Media:
The tables are linked by ID
I created a pivot table listing the Titles > title field and a slicer based on the ...
- 497
0
votes
1
answer
35
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 ...
- 461
0
votes
2
answers
38
views
Create a measure that ignores table filtering and aggregates to a higher level in pivot table - DAX/Power Pivot
I am attempting to rebuild a backend for an excel dashboard with power pivot and need to create a series of columns that give a category level total per personal ID. This means the sum of that ...
- 1
0
votes
0
answers
37
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 ...
0
votes
1
answer
95
views
How to count if your column has the same values on another column in separate related table
I wanted to count the values that has the same position on my table and with my previous related table.
What I did was
=Calculate(Countrows(Table1,Filter(Table1,Table1[Position] = Table2[Position])
...
- 3
0
votes
0
answers
39
views
Calculated field in a pivot table based on power query
I have a sheet with Planned sales data. I have a second sheet with Actual Sales data.
I combined the two sheets using Power Query,
I then created a pivot table but as the data is coming from a model i ...
2
votes
1
answer
62
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
** ...
- 59
2
votes
1
answer
43
views
PowerPivot extract text after different characters
I need to extract text after different delimiters.
I have used this which works for the ones which do have "-" however the data is not all uniform and some have these as delimiters as well:
&...
- 129
0
votes
2
answers
470
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 ...
1
vote
3
answers
146
views
Convert table to reflect column names as row values
I have brand rankings (row values) by customer in the following table:
CustID
Brand1
Brand2
Brand3
Brand4
Brand5
Brand6
Brand10
1
3
1
2
5
4
2
2
1
4
3
5
3
2
3
4
1
5
I would like to transform ...
- 27
0
votes
0
answers
43
views
Calculation on All Values of a Column that Respects Filters on Other Columns
Let us assume that we have a table of data that looks like this:
Let us also assume that we have added the table to the Data Model within Excel (Version 2404 Build 16.0.17531.20152), 64-bit, and that ...
- 139
0
votes
1
answer
36
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 ...
1
vote
1
answer
89
views
In power pivot, is it possible to divide the aggregated results of one row by another within a pivot table?
I have a single table loaded into power pivot in Excel that reports charges, expected revenue (Expected Rev), and volume quantity (VolQty) in a single field (Cat 1). Using this information, I want to ...
- 11
0
votes
1
answer
115
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
0
answers
49
views
Newbie Power Pivot Help - RANKX Function
I'm trying to rank a value within a pivot table to be able to then reference it in a calculated field. I've been unable to use the 'Value Field Settings', 'Show Value As' steps to reference the rank ...
0
votes
1
answer
84
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
254
views
Difference % between 2 columns in Pivot table using Dax measure within Power Pivot
I can't find a formula which calculates the difference % between 2 columns in my pivot table using Power Pivot.
See Data in data model:
See Pivot table where I want to create a measure :
I want to ...
0
votes
0
answers
62
views
PowerPivot - DAX Formula - How to calculate just the monthly sales orders total
I'm attempting to calculate the monthly sales orders total using DAX in PowerPivot. I am able to calculate the running total (YTD), but am stuck on how to just capture the monthly total. I'm currently ...
0
votes
1
answer
17
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
41
views
Previous Week Duration Sum PowerPivot
I need to get the previous week sum total in a column on PowerPivot.
I have tried the below in Both a custom column and as a measure.
my data table has dates, and a duration column as well as a custom ...
- 129
1
vote
1
answer
92
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 ...
- 31
0
votes
0
answers
49
views
Power Pivot Model: How to change existing existing connection source from excel workbook to table loaded as query connection?
enter image description hereWe have a power pivot model already build with DAX formulas and measures where the data sources are excel workbooks on someone's local drive on laptop.
Since this power ...
- 1
1
vote
1
answer
71
views
Power Query Copy Child Data to Parent in Hierarchy
I have a table with parent/child hierarchy where the values in the children in a column (BinNum) needs to be propagated or copied over to the parent. Is there a way to do this in Power Query or DAX?
...
- 65
1
vote
1
answer
151
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
1
answer
57
views
Needs to Display date difference on X-Axis from the selected date slicer in PowerBI
I'm currently working on a Power BI report where I have a line graph representing certain metrics over a period of time. The X-axis of the graph currently displays dates, but I want to change it to ...
0
votes
0
answers
42
views
Please how do I perfectly create a relationship in power pivot between a date table (daily) and a Month table (Month)
I have tried creating a relationship between my date table and Month table but I discovered that the Month table is interpreted as first day of the Month in the date table.
however, I tried creating ...
- 51
0
votes
0
answers
25
views
Is there a DAX function that takes Year, Month, and Week Number as inputs and then return a Date corresponding to the inputs?
I have a Table which my weekly report fetches data from. The Table is updated every week, so the data are in form of Week 1, Week 2,..., Week 5. I have other date information such as Year, and Month, ...
- 11