Running total of Pre-Summed Data

[Anonymous]

Hi all, I'm attempting to make a pareto chart but am having issues computing a running total. The data needs to be groups first by the Reason3_Desc column and summed within each column. However, when I try to run a RANKX command, it generates a weird output since I believe it is calculating the rank within each Reason3_Desc category instead of as a whole. Any help would be great! Thanks!

Source Code is included at the bottom of the post

For reference, here are the measures I currently am using and their outputs: 

TotalDT = SUM([Downtime])

Rank = RANKX(ALLSELECTED(RawDowntimeEventData),[TotalDT])

RunningTotalDT = CALCULATE([TotalDT], TOPN([Rank],ALLSELECTED(RawDowntimeEventData),[TotalDT]))

As well as the output:

The expected output should be along the lines of 

Reason3_Desc	Downtime	Rank	Running Total
Clinker Cooler Drive Malf	310.23	1	310.23
Kiln System Preheat	237.37	2	547.60
Clinker Ring / Ball Removal	68.97	3	616.57

Source Code

StartTime	EndTime	Reason3_Desc	Downtime
7/10/2024 9:05	7/10/2024 9:11	Kiln Feed Pump Malf	0.1
7/3/2024 0:04	7/3/2024 2:29		2.416666667
7/2/2024 19:04	7/2/2024 19:13		0.15
7/2/2024 1:10	7/2/2024 1:21		0.183333333
7/1/2024 6:15	7/1/2024 6:33		0.3
6/30/2024 12:51	6/30/2024 12:57		0.1
6/29/2024 20:17	6/29/2024 20:27		0.166666667
6/27/2024 2:53	6/27/2024 2:59		0.1
6/25/2024 16:07	6/26/2024 20:36	Traveling Grate	28.48333333
6/23/2024 17:05	6/23/2024 19:15		2.166666667
6/18/2024 15:09	6/18/2024 15:16		0.116666667
6/17/2024 23:36	6/17/2024 23:41		0.083333333
6/13/2024 14:45	6/13/2024 14:50		0.083333333
6/13/2024 11:33	6/13/2024 12:25	Traveling Grate	0.866666667
6/12/2024 20:05	6/12/2024 21:46	Traveling Grate	1.683333333
6/12/2024 17:16	6/12/2024 19:43	Kiln System Preheat	2.45
6/12/2024 7:20	6/12/2024 16:56	Clinker Ring/Ball Removal	9.6
6/2/2024 18:27	6/2/2024 18:38	Kiln ID Fan Malf	0.183333333
5/25/2024 17:18	5/25/2024 17:26	Kiln System Preheat	0.133333333
5/24/2024 1:16	5/24/2024 5:18	Internal Power Malfunction	4.033333333
5/21/2024 12:13	5/22/2024 8:48		20.58333333
5/20/2024 17:22	5/20/2024 17:49	Kiln ID Fan Malf	0.45
5/17/2024 10:03	5/17/2024 15:36	Pellitizer Pan System	5.55
5/14/2024 9:35	5/14/2024 9:43	Pellitizer Pan System	0.133333333
5/12/2024 17:07	5/12/2024 19:16	Kiln Feed Conveying, Elevator Malf	2.15
5/8/2024 20:58	5/8/2024 23:24	Clinker Cooler Overload	2.433333333
4/17/2024 1:22	4/17/2024 1:25	Pellitizer Pan System	0.05
4/14/2024 18:02	4/14/2024 21:43	K F System Malfunction	3.683333333
4/14/2024 12:53	4/14/2024 12:57	Pellitizer Pan System	0.066666667
4/9/2024 6:37	4/11/2024 19:14	Kiln System Preheat	60.61666667
4/8/2024 23:28	4/8/2024 23:37	Kiln System Flush	0.15
4/7/2024 17:02	4/7/2024 17:28	Pellitizer Pan System	0.433333333
4/5/2024 12:09	4/7/2024 16:54	Kiln System Preheat	52.75
4/3/2024 23:16	4/4/2024 15:50		16.56666667
4/3/2024 7:26	4/3/2024 7:30	Kiln Feed Pump Malf	0.066666667
3/30/2024 6:51	3/30/2024 13:37	Blend Silo Repairs	6.766666667
3/29/2024 13:29	3/30/2024 4:52	Blend Silo Repairs	15.38333333
3/29/2024 12:30	3/29/2024 12:33	Unknown	0.05
3/21/2024 5:59	3/23/2024 17:21	Clinker Ring/Ball Removal	59.36666667
3/14/2024 11:01	3/14/2024 13:06	Traveling Grate	2.083333333
3/10/2024 7:28	3/10/2024 14:18	Pellitizer Pan System	6.833333333
3/8/2024 2:31	3/8/2024 3:06	Pellitizer Pan System	0.583333333
3/6/2024 3:59	3/6/2024 23:02	Unknown	19.05
2/20/2024 17:25	2/25/2024 18:50	Kiln System Preheat	121.4166667
2/19/2024 20:35	2/19/2024 21:07	Traveling Grate	0.533333333
2/6/2024 6:21	2/6/2024 17:04	Unknown	10.71666667
2/4/2024 10:36	2/4/2024 19:48	CEMS / Analyzer Malfunction	9.2
1/30/2024 6:16	2/1/2024 22:06	Clinker Cooler, general Malf	63.83333333
1/15/2024 1:57	1/28/2024 0:11	Clinker Cooler Drive Malf	310.2333333
1/14/2024 17:36	1/14/2024 17:42		0.1
1/5/2024 9:11	1/5/2024 9:35	Pellitizer Pan System	0.4
1/4/2024 8:49	1/4/2024 18:38	Kiln Feed Pump Malf	9.816666667
1/3/2024 22:34	1/4/2024 8:45	Kiln Feed Pump Malf	10.18333333
1/3/2024 18:26	1/3/2024 20:47	Traveling Grate	2.35

[Ashish_Mathur]

Hi,

Based on the table that you have showed, clearly show the expected result.  Also, what do blanks in the Reason3_Desc mean?

[Irwan · Accepted solution]

hello @Anonymous 

looks like your rank is affected not only by sum of downtime.

try modifiying your rank DAX: 

Rank = RANKX(ALLSELECTED('Table'[Reason3_Desc]),[TotalDT],,DESC)

change value inside ALLSELECTED into your 'Reason3_Desc'

Rank = RANKX(ALLSELECTED(RawDowntimeEventData),[TotalDT])

And let see if this will fix your rank measure.

Hope this will help you.

Thank you.