"power query"

11 Topics

Matrix subtotal showing incorrect totals when using TREATAS() to map Allocation Units per employee
Hi, I am working on a Power BI matrix visual where I need to display Allocation Units per employee and also show correct subtotals at the department level and grand total level. Matrix Structure Rows: DepartmentName EmployeeName Values: Allocation Units (measure) Tables Used Table 1: Activity_Log_Table Contains: DepartmentName EmployeeName Table 2: Resource_Allocation_Table Contains: StaffFullName AllocationUnits There is no direct relationship between these tables, so I am using TREATAS() to map EmployeeName with StaffFullName. Current Measure Allocation Units Measure = CALCULATE( MAX('Resource_Allocation_Table'[AllocationUnits]), TREATAS( VALUES('Activity_Log_Table'[EmployeeName]), 'Resource_Allocation_Table'[StaffFullName] ) ) Problem The measure returns correct values at the EmployeeName level, but: DepartmentName subtotal is incorrect Grand total is incorrect Power BI recalculates totals instead of summing visible rows Expected Behaviour I want the matrix totals to behave like Excel-style aggregation: Show correct allocation units per EmployeeName Show subtotal per DepartmentName as sum of EmployeeName values Show correct grand total as sum of all EmployeeName values Avoid duplicate counting when EmployeeName appears multiple times Sample Data Activity_Log_Table DepartmentName EmployeeName Orion Alex M Orion Nina P Orion Kevin L Nova Daniel R Nova Sophia T Resource_Allocation_Table: StaffFullName AllocationUnits Alex M 40 Nina P 25 Kevin L 15 Daniel R 35 Sophia T 20 Expected Matrix Output DepartmentName EmployeeName Allocation Units Orion Alex M 40 Orion Nina P 25 Orion Kevin L 15 Orion Total 80 Nova Daniel R 35 Nova Sophia T 20 Nova Total 55 Grand Total 135 Currently, subtotals and grand totals are not matching the expected summed values. What is the correct DAX approach to ensure totals aggregate properly when using TREATAS() inside a matrix visual? Thanks, SBC
Solved
SBC
4 months ago Place DAX Commands and Tips
712Views
0likes
3Comments
Unproductive customer
Hi All, In my Power BI report, we have only demention columns (e.g Customer name , code and location) and las invoice date(taken max invoice date from sales fact table). in this report we want to show only unproductive customer, means they has not made any sales in the selected date range. Example: (if we select date range in the slicer, 1s Sept to 30th sept) Customer 001 status is delivered then they are productive customer and customer status is cancelled, returned the they're unproductive customer. want to show only unproductive customer. Tried creating DAX but it's not working. Can anyone please help?
Solved
Vidya_0505
8 months ago Place DAX Commands and Tips
1.2KViews
3likes
6Comments
Help with Last Quarter Formula
I calculated the sum for last quarter and last quarter previous year but they only work when I plot them against a time period. I am trying to plot it against customer name instead and the measures just return blanks. My data has sums of activity by quarterly dates only, not daily. These are my measures that are working but only if plotted against date logic. Net Added Last Quarter = CALCULATE(SUM(OWSSummaryByQuarterByEU[channel_licenses_net_added]), PREVIOUSQUARTER(OWSSummaryByQuarterByEu[End of Quarter].[Date])) Net Added Last Quarter Prior Year = CALCULATE([Net Added Last Quarter], SAMEPERIODLASTYEAR(OWSSummaryByQuarterByEU[End of Quarter].[Date])) Does anyone have a formula that would give me the sum of last quarter when not plotted against time? I'm assuming it needs to be a calculated column but everything I try is returning blank or error. Willing to try DAX or Power Query solutions.
Anonymous
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
7Comments
Calculating Current Value using Previous Row's Calculated Value
I have a dataset representing battery state changes, and I need to calculate the ResidualCapacity for each state change. The ResidualCapacity should be calculated as the previous ResidualCapacity plus the current ChargeVariation, without exceeding an UpperBound of 80. Here's the structure of my table with the correct ResidualCapacity value: ChangeState ChargeVariation ResidualCapacity 1 70 70 2 -5 65 3 20 80 4 -10 70 5 -5 65 ChangeState: Index showing the chronological order of battery state changes. ChargeVariation: The amount by which the charge has varied. ChargeVariation < 0: Battery usage ChargeVariation > 0: Battery charging ResidualCapacity: A calculated column that should show the residual battery capacity after each state change. My goal is for ResidualCapacity to always be the previous ResidualCapacity plus the current ChargeVariation, but it should never exceed the UpperBound of 80. I've written the following DAX query, but it returns an incorrect result for the last value of ResidualCapacity (it returns 70 instead of 65): ResidualCapacity = VAR UpperBound = 80 VAR CurrentVariation = 'Table'[ChargeVariation] VAR CurrentState = 'Table'[ChangeState] VAR PervVariationRT = SUMX( FILTER( 'Table', 'Table'[ChangeState] < CurrentState ), 'Table'[ChargeVariation] ) VAR Result = MIN(CurrentVariation + MIN(PervVariationRT, UpperBound), UpperBound) RETURN Result ChangeState ChargeVariation ResidualCapacity (WRONG) 1 70 70 2 -5 65 3 20 80 4 -10 70 5 -5 70 How can I modify this query to correctly calculate the ResidualCapacity calculated column for each state change? It is also OK if the solution is written in M Power Query
Solved
Poffarbacco
1 year ago Place DAX Commands and Tips
1.6KViews
1like
4Comments
Custom slicer to select and display multiple columns
Hello, I am trying to create a custom slicer based on multiple columns. Here is the data I have currently: aa bb cc dd 2020 A 2020 B 2020 C 2021 A 2021 B 2021 C 2022 A 2022 B 2022 C a1 b1 c1 d1 Customer 1 a2 b2 c2 d2 Customer 2 a3 b3 c3 d3 Customer 3 I want to create a slicer such that I can select one or multiple specific years. For example, if I select "2020" in the slicer, the output would look like this: aa bb cc dd 2020 A 2020 B 2020 C a1 b1 c1 d1 Customer 1 a2 b2 c2 d2 Customer 2 a3 b3 c3 d3 Customer 3 How can I do this? Really appreciate your help - thanks!
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.9KViews
0likes
8Comments
Merge multiple rows with alternating null values in Power BI
Hello, This is the data I have currently. I am trying to merge every 3 rows for each customer order. Because all cell values are the same for each order's aa to ee, I would like the data to be cleaned in a way that displays one order per row. aa bb cc dd ee 2020 2021 2022 a1 b1 Customer 1 d1 e1 1 null null a1 b1 Customer 1 d1 e1 null 1 null a1 b1 Customer 1 d1 e1 null null 1 a2 b2 Customer 2 d2 e2 2 null null a2 b2 Customer 2 d2 e2 null 2 null a2 b2 Customer 2 d2 e2 null null 2 a3 b3 Customer 3 d3 e3 3 null null a3 b3 Customer 3 d3 e3 null 3 null a3 b3 Customer 3 d3 e3 null null 3 a4 b4 Customer 4 d4 e4 4 null null a4 b4 Customer 4 d4 e4 null 4 null a4 b4 Customer 4 d4 e4 null null 4 This is the output I'm looking for: aa bb cc dd ee 2020 2021 2022 a1 b1 Customer 1 d1 e1 1 1 1 a2 b2 Customer 2 d2 e2 2 2 2 a3 b3 Customer 3 d3 e3 3 3 3 a4 b4 Customer 4 d4 e4 4 4 4 How can I do this? Really appreciate your help!
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1KViews
0likes
4Comments
Updating table rows based on earlier row
Hi all, I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source. The solution needs to: Find instances where a flag has indicated a manual adjustment has been made to a user entry Identify any entries associated with this user ID between the date on which the manual adjustment was made and the date on which the source data has been updated to reflect the manual adjustment (if applicable) Apply a specific set of adjustments to the identified rows, dependent on the nature of the adjustment. Considering the attached table, I’ve tried to outline the logic I’m trying to implement below: For the purposes of this explanation, I’ll refer to the manual adjustment as row X. IF Flag = “Adj” ( IF Case = “L” ( For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case Output = “H” ) IF Case = “M” ( For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case Level = X.Level ) ) Any advice on how to optimally achieve this within PowerBI would be greatly appreciated. IDCaseFlagLevelDateOutput 1 HC Source 1 03/07/2024 S 1 HC Source 1 04/07/2024 S 1 L Adj 1 05/07/2024 S 1 HC Source 1 06/07/2024 S 1 HC Source 1 07/07/2024 S 1 L Source 1 08/07/2024 S 1 HC Source 1 09/07/2024 S 1 HC Source 1 10/07/2024 S 1 HC Source 1 11/07/2024 S 1 HC Source 1 12/07/2024 S 2 HC Source 3 03/07/2024 S 2 HC Source 3 04/07/2024 S 2 HC Source 3 05/07/2024 S 2 HC Source 3 06/07/2024 S 2 HC Source 3 07/07/2024 S 2 L Source 3 08/07/2024 S 3 HC Source 3 03/07/2024 S 3 HC Source 3 04/07/2024 S 3 HC Source 3 05/07/2024 S 3 HC Source 3 06/07/2024 S 3 HC Source 3 07/07/2024 S 3 L Adj 3 08/07/2024 S 3 HC Source 3 09/07/2024 S 3 HC Source 3 10/07/2024 S 3 HC Source 3 11/07/2024 S 3 HC Source 3 12/07/2024 S 4 HC Source 5 03/07/2024 S 4 HC Source 5 04/07/2024 S 4 L Adj 5 05/07/2024 S 4 HC Source 5 05/07/2024 S 4 HC Source 5 06/07/2024 S 4 HC Source 5 07/07/2024 S 4 HC Source 5 08/07/2024 S 4 HC Source 5 09/07/2024 S 4 HC Source 5 10/07/2024 S 4 HC Source 5 11/07/2024 S 4 L Source 5 12/07/2024 S 5 HC Source 2 03/07/2024 S 5 M Adj 3 04/07/2024 S 5 HC Source 2 04/07/2024 S 5 HC Source 2 05/07/2024 S 5 HC Source 2 06/07/2024 S 5 HC Source 2 07/07/2024 S 5 HC Source 2 08/07/2024 S 5 M Source 3 09/07/2024 S 5 HC Source 3 10/07/2024 S 5 HC Source 3 11/07/2024 S 5 HC Source 3 12/07/2024 S 5 HC Source 3 13/07/2024 S
Solved
PaulBoden
2 years ago Place DAX Commands and Tips
1.2KViews
0likes
5Comments
Transform multiple columns based on values in another column
Hello, I am trying to group, split, or somehow transform multiple columns (Years 2020-2023) based on values in another column (Source) for visualization in Power BI. The original data comes from Excel. How can I do the following? This is the raw data I have right now, where I have multiple orders per customer, and each order being estimated based on Source A, B, and C. Specifically, I have 3 orders from Customer 1, and rows 2-4 are about the 1st order. In these 3 rows, values are the same for columns aaa, bbb, ddd, eee, fff, and ggg, so it is essentially a repetition. I have numbers in columns 2020-2023, but I don't want to view Source horizontally. Instead, I would like to incorporate Source into the Year columns. This is the end product I want. Each order is only 1 row, and Source is incorporated into the Year columns such that I have 2020A, 2020B, 2020C, 2021A... and so on. Really appreciate your help. Thanks!
Anonymous
2 years ago Place DAX Commands and Tips
1.7KViews
0likes
7Comments
How to use SUMMARIZE inside a calculated column?
I'm trying to get information from aggregated version of my table into my original table as a column, but im not sure how to do it. Find the sample ecxample below The table contains 4 column - EMP_ID, DATE, MONTHLY SALARY, DESIGNATION I want to create new column called TOTAL SALARY which is the sum of salary for each employee available in data. There is time filter as well, like if I select 6 months in the filter visual the total salary should be populated as total six months salary. I couldn't find a way to do it in PBI using DAX/POWER QUERY. Please help me on this!! Thanks
Solved
aji549
2 years ago Place DAX Commands and Tips
1KViews
0likes
2Comments
Dynamic Measure Required with two slicer
Hi Folks, I'm trying to develop the dashboard by comparing two months and their uses of two slicers. I prepared the dashboard using Excel Formulas. please help to develop Power-BI As the Raw data is attached Excel is available. please refer it Google Drive Link for Excel File Please feel free to contact for more info if required. RAW Data Period Account Value X Value Y Value Z Fix Tata 4 3 5 Fix Birla 6 7 8 Fix Adani 7 8 9 Fix RIL 8 9 10 Fix Tata 9 10 11 Fix Birla 10 11 12 Jan Adani 11 12 13 Jan RIL 12 13 14 Jan Tata 13 14 15 Jan Birla 14 15 16 Jan Adani 15 16 17 Feb RIL 16 17 18 Feb Tata 17 18 19 Feb Birla 18 19 20 Feb Adani 19 20 21 Feb RIL 20 21 22 March Tata 21 22 23 March Birla 22 23 24 March Adani 23 24 25 March RIL 24 25 26 March Tata 25 26 27 March Birla 26 27 28 March Adani 27 28 29 April RIL 28 29 30 April Tata 29 30 31 April Birla 30 31 32 April Adani 31 32 33 April RIL 32 33 34 April Tata 33 34 35 Regards, MOHIT
Solved
mohitchugh
2 years ago Place DAX Commands and Tips
901Views
0likes
2Comments