All Questions
Tagged with powerpivot powerbi
350 questions
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
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
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
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
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
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
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
0
votes
1
answer
681
views
How to create a DAX formula to create another column based on the value in another column
How do I create a formula in Dax that gives me the % achieved column, based on the location. When it is EU the % achieved formula should be actual*target else it should be actual+target
More ...
- 75
0
votes
1
answer
166
views
How Can I make measure SUM my amount according to level and parent category? using power bi
I have two tables in power bi, the Invoices table and the Categories table. In the Invoices table, I have a column for CategID and a column for sales amount. In the Categories table, I have columns ...
user19033089
0
votes
0
answers
54
views
Data manipulation with power pivot, power query
I am doing some simple projects with power pivot/query/bi. When I try to create the pivot table from the data model in power pivot, it will only display the data as below. Counted value will be ...
- 325
0
votes
0
answers
86
views
I have unmatched rows from my fact table showing up unless I put a useless calculation in the values section of my pivot table
I should begin by saying that I'm very new to Power Pivot and Power Query.
I have a dim table that contains work orders and a fact table that contains documents that are related to the work orders. ...
1
vote
1
answer
43
views
Need dataset with multiple subdivisions filtered down to limited half-integer range before charting
Please see below for dataset and desirable visual.
I just need some guidance/path in constructing a data model and subsequent visuals for the following dataset.
I have various Vessel Names, divided ...
- 133
1
vote
1
answer
71
views
How would I extract values within 0.20 of every half-integer, then extract again only values nearest to each half-integer? (with DAX)
In below fact table, how would I construct a DAX calculated column to:
extract [Order] values/rows within 0.20 of every half-integer {0.5, 1.0, 1.5, 2.0, 2.5, 3.0}
extract again [Order] values/rows ...
- 133
2
votes
1
answer
503
views
Certain Dax Measure Ignoring Filters
I am very new to Power BI, Power Pivot & DAX and I feel like I am missing a fundamental concept of how filter context and row context is working so some help is appreciated.
Here is what I have ...
- 23
1
vote
0
answers
332
views
DAX Formula for COUNT Ignoring Column Filters but Respecting Row Filters and Slicers
I need a DAX formula to count rows respecting all slicers and the rows in question only.
The example below shows the source data (top) and the expected result (bottom), which is filtered to 2022 by a ...
- 11
0
votes
1
answer
45
views
PowerPivot/DAX - distribution of some groups/channels to other ones
I've got the following sales table (from datacube):
Channel | Center | Qty
=========+========+=====
D2D | H100 | 100
D2D | H200 | 400
Ext | T100 | 320
Ext | T200 | 280
...
- 6,429
0
votes
1
answer
118
views
Excel: DataModel - why it returns cartesian product?
Can't understand how DataModel works in Excel.
Please help
Example:
in Excel - why it returns cartesian product?
At the same time absolutely the same DataModel in PowerBi returns normal estimated ...
- 751
0
votes
2
answers
119
views
5/multi-year year forecast average measure in DAX
I have a [Profit] measure in my 'Data' table and a linked 'Calendar' table with a column YEARS that I use as the column header in my pivot. In total, there are 8 years.
I want to calculate for each ...
- 63
1
vote
1
answer
737
views
Power BI: Using Slicers to Choose Which Measure to Display
I've been outrageously helped by David Bacci for a dashboard that helped me compare one date range to another.
The hardest part is done for now and we are quite happy with the results.
However I need ...
0
votes
2
answers
168
views
Logic to find FIRST PASS RATE (Number of batch passed test the first time they came for testing?) for quality data in power query editor
enter image description here
I'm working on a quality set of data and trying to find the first pass rate. Attached picture shows a sample of lab testing data. Batch_id is a unique Id for every batch ...
0
votes
1
answer
46
views
Total sum of measure
I'm exploring different ways of dealing with a Pareto chart. This time I'm trying to obtain the total sum of a column based on a measure.
The sales table that I have:
Article
Total
Article_4
1000.00
...
- 461
0
votes
1
answer
355
views
Converting Switch(true(), SELECTEDVALUE statement from PowerBI to Excel
I have a data model in PowerBI to automate a specific table, which utilizes the following code to dynamically switch between summing line items from my fact table and subtotals within the table I am ...
- 1
2
votes
3
answers
674
views
Accumulate values over years in DAX
My table has a column that calculates the cumulative total year-to-date (YTD). The column is Balance BRL. It works ok using the DAX function DATESYTD.
The column Balance BRL is perfectly achieved ...
- 651
1
vote
0
answers
33
views
Problem with multiple references in powerpivot. CROSSFILTER not working
I'm trying to calculate costs of diferent work types for the employees. The Employees are part of groups. Each group has work types. Each one with it own cost.
Tables:
Employee Name Group
A ...
- 11
0
votes
0
answers
41
views
Subtotal a Conditional Measure by Calendar Periods
For some context here are the tables involved (aside from the built-in Calendar Table)
LocationDim
Location Code
Location Name
Category
LOC1
Location 1
CAT1
LOC2
Location 2
CAT2
SalesFact
Period
...
- 43
0
votes
1
answer
270
views
Many to many relationship in Power Pivot (Excel data model) - Replicate Power BI behavior in Excel
In power BI, I can use a calendar with LTM (last 12 months) and link it to a fact table (Sales for example). There are multiple transactions in the Sales table on the same date (to multiple customers) ...
- 1
-1
votes
1
answer
631
views
Relative Date Item in Power Pivot GETPIVOT DATA excel function
I am using a GETPIVOTDATA function in Excel to source data from a pivot table generated by a Power BI query (everything was originally only in excel, the file got too large, so i stored the main ...
- 23
0
votes
1
answer
1k
views
Power Query Column Average Cannot Convert Null to Type List
I have a column (First Salary) that has either a number of a null value. I added another column (CSM_SALARY) to check if it is null, and if so, take the average of First Salary, if not, take the value ...
0
votes
0
answers
166
views
Power Pivot - Is it possible to setup a date relationship to a table with a FROM and TO date
I have been searching the internet for support with this but cannot find anything that fits the bill. I'm not sure if what I want to do is possible, so if someone can please help
I have a table of ...
- 1
1
vote
1
answer
437
views
DAX: Using Calendar Month as a column and as a criteria for counting records based on current row
So I have the following pivot table report through my data model. I want my measure 'Branches
Per Cluster' to consider the current column of month or year.
I have the following tables aside from a ...
- 43
0
votes
2
answers
534
views
DAX count of TransactionID's based on sum of value
In this table example, the DISTINCTCOUNT of TransactionID = 3 (basket count), however TransactionID 72461 has 3 x Unit sales (of different products) making it a multi-unit basket. I am looking to ...
1
vote
1
answer
97
views
Power Query not Showing Date Properly After Transposed
I have the following query table as shown on the first image. But I want that query transposed to horizontal as I want. So I transposed that query table by transposing like on the second image. It is ...
- 23
1
vote
0
answers
85
views
Multiple Fact Tables Slice one table with second table categories
I looked around A LOT but could not find the answer, I'd appreciate some help here!
I have a data model with 2 fact tables: [A] Product Sales & [B] Expenses Per Product Per Channel.
I need to ...
0
votes
1
answer
149
views
DAX Measure - Extrapolate last Date for COUNT
I have a table that contains 3 columns:
Order
Date
State
Each row / record shows if the state was changed:
Now I would like to calculate the number of order that are below state 3 for each date in ...
- 331
1
vote
1
answer
162
views
DAX Iterator Behavior
I don't think this is doing what I want it to do. I have a fact table with columns date, weekstarting, customer, product and qtyshipped and I want an average product sales including dates with 0 sales....
- 11
1
vote
1
answer
102
views
dax fails in excel but not in power bi?
Whenever I consume my model from excel, if I put the next measure it crashes:
IF
(
NOT [Measure1] < 0,
DIVIDE ( [Measure2], [Measure1])
)
Why is this happening?
(it works fine in power bi ...
- 656
0
votes
2
answers
420
views
Microsoft Power BI - DAX - develop a dataset using variables - NATURALJOIN, GENERATE, GENERATEALL
I have a Power BI Desktop file. I am developing a Calculated Table (CT) in DAX language.
I am using a number of manipulations inside to develop this CT (similar to what I do in a T-SQL stored ...
- 441
0
votes
1
answer
1k
views
DAX language- Microsoft Power BI - SUMMARIZE inside SELECTCOLUMNS - simple syntax
I have a situation below (Power BI - DAX) in which I am trying to SUMMARIZE a table called Product with a SUM aggregation to get the total cost of all products in each Category; but then I have to ...
- 441
1
vote
1
answer
439
views
DAX Measure - Specific Ranking using RANKX
I have a table that just contains all articles.
Then I have a transactional table that contains a value that I would like to use for the rank. THe same tables has the department information.
The ...
- 331
0
votes
0
answers
160
views
DAX - RANKX with 2 Measures
I have a matrix that shows me for each article 2 values:
I have a measure "Value 1" that shows me a number and I have a second measure.
Now I would like to get the ranking first based on ...
- 331
0
votes
0
answers
204
views
One to many relationship excel
I have a list of part's and their quantities. These parts are used to build sub assemblies and are also used with the sub assemblies to build the final product. These parts are used on multiple ...
0
votes
1
answer
559
views
Power BI: Max value of all children
I have a dim table with hierarchy as
ID
Parent
A
B
A
C
B
D
B
E
A
A is the top level, while B and E are its children, C, D are the grandchildren.
Then I have a fact table
ID
Value
A
1
B
2
C
...
- 75
0
votes
2
answers
459
views
How to Get Custom Week Desc in DAX
Basis on below data I want to add a calculated column with Week Description
I've done it in excel by typing it manually.
Also my week is starting from Thursday and ends on Wednesday hence I've Used ...
- 641
0
votes
1
answer
256
views
POwer pivot Calculated field using aggregates
I have a source table loking like this
Date
Measure Name
Measure value
01/01/2020
Revenue
1250
01/01/2020
Sales number
43
01/01/2020
Costs
1000
01/02/2020
Revenue
4500
01/02/2020
Sales number
...
0
votes
3
answers
835
views
Refresh power pivot-power query
I have a table in power query that is fed from some Excel files, with this data I make an inner join with other catalog tables that I have and do operations on calculated columns and then add to the ...
- 37
0
votes
1
answer
107
views
DAX formula / Power BI calculation : calculate Quarter 3 from 2 different tables (Actual and Estimate)
I hope you can help me with below matter. I am trying to figure out a calculation for Quarter 3 (July + Aug + Sept).
I have 2 tables Actual and Estimate, the data in Actual is till August 2021. When ...
- 1
0
votes
0
answers
41
views
Transferring DAX formulas from Powerbi to Power Pivot in Excel
I currently have this set of codes in Powerbi which is functional. However, I would like to transfer the DAX codes to Excel as a measure for Power Pivot. However, the syntax error kept appearing. Can ...
0
votes
1
answer
917
views
Power Pivot Max Value Selected In Slicer
I have the following slicer in excel and the users select more than one option
How can I get with DAX the maximum value selected in the slicer? and save it in a variable
with the example I should get ...
- 37
1
vote
0
answers
171
views
Importing data from .xls files when gettimg data from a folder in power query
I have tried to get data from a particular folder containing only .xls using power query without any luck. Right now I'm running a macro to save as .xlsx before refreshing the data, however, I'd like ...
