Two Aggregations: Cumulative Sum of Averages Per Category Per Date

[CuriousFlee]

Good morning,

Have the following underlying data:

sedol	returns	region	sector1	date
2026361	1.5	North America	Financials	01/06/2023
2002479	1.2	North America	IT	01/06/2023
2831811	1.1	Europe	IT	01/06/2023
2002305	-2	Europe	Financials	01/06/2023
B92SR70	-3	APAC	Materials	01/06/2023
2026361	-1	North America	Financials	02/06/2023
2002479	-2	North America	IT	02/06/2023
2831811	3	Europe	IT	02/06/2023
2002305	2	Europe	Financials	02/06/2023
B92SR70	1	APAC	Materials	02/06/2023
2026361	0	North America	Financials	03/06/2023
2002479	0.5	North America	IT	03/06/2023
2831811	0.1	Europe	IT	03/06/2023
2002305	-5	Europe	Financials	03/06/2023
B92SR70	2	APAC	Materials	03/06/2023

Requirement:

• aggregate it in the following manner: first get the average return for a day for region and then add cumulative sum of it.
• aggregation should be reactive and respond to external explicit filters (slicers etc)

Sample of output:

region	date	average_rets_per_day	cum_rets
APAC	01/06/2023	-3.00	-3.00
APAC	02/06/2023	1.00	-2.00
APAC	03/06/2023	2.00	0.00
Europe	01/06/2023	-0.45	-0.45
Europe	02/06/2023	2.50	2.05
Europe	03/06/2023	-2.45	-0.40
North America	01/06/2023	1.35	1.35
North America	02/06/2023	-1.50	-0.15
North America	03/06/2023	0.25	0.10

Can achieve individual tasks (eg running sum, calc averages) but struggling to get everythign together in responsive output.

Help is very much appreciated.

[CuriousFlee]

v-rongtiep-msft, this is absolutely brilliant. Had a feeling window function must be involved, but you've just nailed. 

Great suggestion, thanks!

[Anonymous · Accepted solution]

Hi @CuriousFlee ,

I have created a simple sample, please refer to see if it helps you.

Create 2 measures.

Measure = 
CALCULATE(AVERAGE('Table'[returns]),FILTER(ALL('Table'),'Table'[region]=SELECTEDVALUE('Table'[region])&&'Table'[date]=SELECTEDVALUE('Table'[date])))

Measure 2 = 
var tmp=WINDOW(1,abs,0,REL,ALLSELECTED('Table'[date],'Table'[region]),,,PARTITIONBY('Table'[region]))
return
SUMX(tmp,[Measure])

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.