Power Pivot: Cumulative Total

[jajatidev]

Hi,
Looking for assistance with an issue I'm facing with cumulative total.
The function I have used is;

CALCULATE(SUMX('Ageing_Trend_Report',[Unconfirmed_Qty.]), FILTER(ALL(Ageing_Trend_Report),Ageing_Trend_Report[Report_Week]<= MAX(Ageing_Trend_Report[Report_Week])))

and the result I'm getting is;

Report_Week	Age_Group	Unconfirmed_Qty.	Total Unconfirmed	% By Group	% Running Total (DESC) by Report_Week
01/22/2024	[ > 120]	140	63,637
01/22/2024	[0 - 5]	3,324	63,637
01/22/2024	[11 - 20]	1,219	63,637
01/22/2024	[21 - 30]	4,175	63,637
01/22/2024	[31 - 60]	899	63,637
01/22/2024	[6 - 10]	7,760	63,637
01/22/2024	[61 - 90]	3,257	63,637
01/22/2024	[91 - 120]	232	63,637
01/29/2024	[ > 120]	154	63,637
01/29/2024	[0 - 5]	4,185	63,637
01/29/2024	[11 - 20]	8,436	63,637
01/29/2024	[21 - 30]	1,910	63,637
01/29/2024	[31 - 60]	2,565	63,637
01/29/2024	[6 - 10]	1,041	63,637
01/29/2024	[61 - 90]	3,254	63,637
01/29/2024	[91 - 120]	220	63,637
02/05/2024	[ > 120]	131	63,637
02/05/2024	[0 - 5]	3,394	63,637
02/05/2024	[11 - 20]	7,771	63,637
02/05/2024	[21 - 30]	883	63,637
02/05/2024	[31 - 60]	3,848	63,637
02/05/2024	[6 - 10]	2,233	63,637
02/05/2024	[61 - 90]	2,470	63,637
02/05/2024	[91 - 120]	136	63,637

However, shared below is the result I'm expecting so that %age of the age group can be calculated by date. Then a running total of the percentage can be generated by week date in descending order.
I also need guidance with the Dax function for the running total of the percentage.

Report_Week	Age_Group	Unconfirmed_Qty.	Total Unconfirmed	% By Group	% Running Total (DESC) by Report_Week
01/22/2024	[ > 120]	140	21,006	0.67%
01/22/2024	[0 - 5]	3,324	21,006	15.82%
01/22/2024	[11 - 20]	1,219	21,006	5.80%
01/22/2024	[21 - 30]	4,175	21,006	19.88%
01/22/2024	[31 - 60]	899	21,006	4.28%
01/22/2024	[6 - 10]	7,760	21,006	36.94%
01/22/2024	[61 - 90]	3,257	21,006	15.51%
01/22/2024	[91 - 120]	232	21,006	1.10%
01/29/2024	[ > 120]	154	21,765	0.71%
01/29/2024	[0 - 5]	4,185	21,765	19.23%
01/29/2024	[11 - 20]	8,436	21,765	38.76%
01/29/2024	[21 - 30]	1,910	21,765	8.78%
01/29/2024	[31 - 60]	2,565	21,765	11.78%
01/29/2024	[6 - 10]	1,041	21,765	4.78%
01/29/2024	[61 - 90]	3,254	21,765	14.95%
01/29/2024	[91 - 120]	220	21,765	1.01%
02/05/2024	[ > 120]	131	20,866	0.63%
02/05/2024	[0 - 5]	3,394	20,866	16.27%
02/05/2024	[11 - 20]	7,771	20,866	37.24%
02/05/2024	[21 - 30]	883	20,866	4.23%
02/05/2024	[31 - 60]	3,848	20,866	18.44%
02/05/2024	[6 - 10]	2,233	20,866	10.70%
02/05/2024	[61 - 90]	2,470	20,866	11.84%
02/05/2024	[91 - 120]	136	20,866	0.65%

 

[JEC]

First add an index column. Call it "IndexCol"

=COUNTROWS(FILTER(Table1, EARLIER([Report_Week]) =  [Report_Week] && EARLIER([Age_Group]) >= [Age_Group]))

Then you can use this formula to get your %ByGroup (also call the column "%ByGroup")

=[Unconfirmed_Qty.]/CALCULATE(SUM([Unconfirmed_Qty.]),FILTER(ALL(Table1),EARLIER([Report_Week]) = [Report_Week]))

For running total afterwards

=CALCULATE(SUM([%ByGroup]),FILTER(Table1,EARLIER([Report_Week]) = [Report_Week] && EARLIER([IndexCol]) >= [IndexCol]))

If you want Descending running totals, you have to write your running total like

=CALCULATE(SUM([%ByGroup]);FILTER(Table1;EARLIER([Report_Week]) = [Report_Week] && EARLIER([IndexCol]) <= [IndexCol]))

 

[jajatidev]

First add an index column. Call it "IndexCol"

=COUNTROWS(FILTER(Table1, EARLIER([Report_Week]) =  [Report_Week] && EARLIER([Age_Group]) >= [Age_Group]))

Then you can use this formula to get your %ByGroup (also call the column "%ByGroup")

=[Unconfirmed_Qty.]/CALCULATE(SUM([Unconfirmed_Qty.]),FILTER(ALL(Table1),EARLIER([Report_Week]) = [Report_Week]))

For running total afterwards

=CALCULATE(SUM([%ByGroup]),FILTER(Table1,EARLIER([Report_Week]) = [Report_Week] && EARLIER([IndexCol]) >= [IndexCol]))

View attachment 106433

Thanks, @JEC, appreciate the quick response.
One adjustment is needed with the indexing sequence. "[0 - 5]" needs to be indexed as 1 and "[ > 120]" needs to be indexed as 8.

 

[JEC]

See my last post below. I was already assuming this.
I have ";" as seperator, you need to change this to ","

Could not change it anymore...

PS: the %ByGroup could be written like

=[Unconfirmed_Qty.]/[Total Unconfirmed]

 

[JEC]

For indexing as you desire you could use this formula

=SWITCH([Age_Group],
   "[ > 120]",8,
   "[0 - 5]",1,
   "[11 - 20]",2,
   "[21 - 30]",3,
   "[31 - 60]",4,
   "[6 - 10]",5,
   "[61 - 90]",6,
   "[91 - 120]",7
   )

 

[jajatidev]

For indexing as you desire you could use this formula

=SWITCH([Age_Group],
   "[ > 120]",8,
   "[0 - 5]",1,
   "[11 - 20]",2,
   "[21 - 30]",3,
   "[31 - 60]",4,
   "[6 - 10]",5,
   "[61 - 90]",6,
   "[91 - 120]",7
   )

Thanks again @JEC.

Can this also be executed in the power query editor without using group by?

 

[JEC]

not without group by…why do you not want to use it?
After grouping and executing these steps, you can easily extract the complete table again

 

[jajatidev]

not without group by…why do you not want to use it?
After grouping and executing these steps, you can easily extract the complete table again

I was just thinking aloud of other possible approaches.
Thanks for the calculations, I have applied them.