Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on compiling the cumulative total for FAULT_OPS of equipment over a period of 4 years. The reading is taken monthly and the FAULT_OPS counter is reset. So we have the count for that month only. The table consists of 220 K readings for 6400 Measuring Points. Each Measuring Point corresponds to a specific Euipment No and a Characteristic. The table below shows the readings for two Characteristics for one Equipment No for various months.
CHARACTERISTIC | READ_DATE | READING | MEASURING_POINT |
|
|
FAULT_OPS | 20170104 | 0 | 2650209 |
|
|
OTHER | 20170104 | 34 | 2650210 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
FAULT_OPS | 20170203 | 1 | 2650209 |
|
|
OTHER | 20170203 | 23 | 2650210 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
FAULT_OPS | 20170303 | 1 | 2650209 |
|
|
OTHER | 20170303 | 15 | 2650210 |
|
|
|
|
|
|
|
|
Between each set of the Readings for one Equipment, there are thousands of Readings for other Equipment Nos with different MEASURING_POINT numbers taken during the same month.
We need to create a new Column in Query Editor. Sort the table in Ascending order for the READ_DATE starting from month 201701. Compile a custom function for this new Column which will show the running total of FAULT_OPS for each MEASURING_POINT.
I am at a loss how to include all these conditions in IF.. ELSE statement for the Custom Column.
Please advise me how to go about it and suggest a solution.
Thanks in advance
Solved! Go to Solution.
I searched the internet and the post by Philip Treacy helped me resolve the issue. The reference is:
https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query
It has taken some time for the query to run probably due to the large number of records, but this was done. Thanks to the author for a detailed explanation of the various steps in the query.
Hi @kkanda ,
According to the error message, please refer the method in this thread to get the actual error cause. And please try the method in the following links and check whether they can help you get the expected result.
Grouped Running Totals in Power Query
How to do a running Sum by group in Power Query?
In addition, you can create a measure as below by DAX to get it:
Culmulative =
CALCULATE (
SUM ( 'Table'[READING] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[MEASURING_POINT] = SELECTEDVALUE ( 'Table'[MEASURING_POINT] )
&& 'Table'[READ_DATE] <= SELECTEDVALUE ( 'Table'[READ_DATE] )
)
)
Best Regards
On searching the internet, I found the following Custom function to apply for compiling the running total for each group:-
= (MyTable as table, SumColumn as text, GroupColumn as text) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source,SumColumn),{0},
(cumulative,SumColumn) => cumulative & {List.Last(cumulative) + SumColumn})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType),
RemoveGroupColumn = Table.RemoveColumns(AddedRunningSum,{GroupColumn}),
FunctionResult = RemoveGroupColumn
in
FunctionResult
I applied this function with the parameters as the source Table as Table, READING as SumColumn and MEASURING_POINT as GroupColumn. I get the error "Evaluation resulted in a stack overflow and cannot continue".
The query runs for some time before the above error appears.
Is this happening because the table has about 220K rows and 6K groups?
I searched the internet and the post by Philip Treacy helped me resolve the issue. The reference is:
https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query
It has taken some time for the query to run probably due to the large number of records, but this was done. Thanks to the author for a detailed explanation of the various steps in the query.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |