Help to calculate Average in a accumulated measure

[gomezc73]

Hi Experts,

I need help with the following problem, any idea on how to solve it would be appreciated.

I have a table that has an amount per day, so I made a measure to generate an accumulated amount by day .

Something like this:

Now, The users needs see an Average by month, considering only the data filtered.

Let me explain, if the user select show the data only for TUESDAY, he need see an AVERAGE like this:

If the User select all days (Sun-Sat), I need sum all accumulated amount divided by the num of days of the month.

This is the data for JAN & FEB 2023 with the accumulated amount in TUESDAY.

Year	QT	Month	Week#	Week Day	Date	Beg. Balance	Daily Amount	Accumulated Amount
2023	Q1	Jan	1	Tue	01/03/2023	(3,633,363,058.57)	(171,002,732.78)	(3,804,365,791.35)
2023	Q1	Jan	2	Tue	01/10/2023	(3,633,363,058.57)	(206,220,918.69)	(3,839,583,977.26)
2023	Q1	Jan	3	Tue	01/17/2023	(3,633,363,058.57)	(266,672,453.96)	(3,900,035,512.53)
2023	Q1	Jan	4	Tue	01/24/2023	(3,633,363,058.57)	(357,264,773.29)	(3,990,627,831.86)
2023	Q1	Jan	5	Tue	01/31/2023	(3,633,363,058.57)	(220,729,172.89)	(3,854,092,231.46)
2023	Q1	Feb	6	Tue	02/07/2023	(3,633,363,058.57)	(485,377,556.93)	(4,118,740,615.50)
2023	Q1	Feb	7	Tue	02/14/2023	(3,633,363,058.57)	(550,096,607.34)	(4,183,459,665.91)
2023	Q1	Feb	8	Tue	02/21/2023	(3,633,363,058.57)	(634,502,523.92)	(4,267,865,582.49)
2023	Q1	Feb	9	Tue	02/28/2023	(3,633,363,058.57)	(523,876,044.55)	(4,157,239,103.12)

Thanks in advance

Regards

[v-mengmli-msft]

Hi @gomezc73 ,

Do you want to calculate average monthly Accumulated Amount? What's the denominator? Assuming it is the days of the month are the rows of data per month, you can do this. 

Average Accumulated Amount = 
VAR current_month =MAX('Table'[Month])
VAR selected_week =ALLSELECTED('Table'[Week Day])
VAR total= CALCULATE(SUM('Table'[Accumulated Amount]),ALL('Table'),'Table'[Month]=current_month&&'Table'[Week Day] in selected_week)
VAR countR = CALCULATE(COUNTROWS('Table'),ALL('Table'),'Table'[Month]=current_month&&'Table'[Week Day] in selected_week)
RETURN
total/countR

Best regards,

Mengmeng Li

[gomezc73]

Hi,

 Thank you for your response,

 In this instruction I get an error, due the 'Accumulate amount; is in a 'measure variable' and The command SUM only acept a 'COLUMN VARIABLE'. How i can change it?

VAR total= CALCULATE(SUM('Table'[Accumulated Amount]),

[v-mengmli-msft]

Hi @gomezc73 ,

Is 'Accumulate amount' a measure? If so, you can try this.

VAR total= CALCULATE(SUMX('Table',[Accumulate amount]),ALL('Table'),'Table'[Month]=current_month&&'Table'[Week Day] in selected_week)

If the above DAX does not solve the problem, please provide more information about 'Accumulate amount'. For example, how it was created and what else it is used for.

Best regards,

Mengmeng Li

[gomezc73]

Hi, Thank you for time. I tried with your response but it is generating wrong Averages.

I have two tables: 

A Calendar table (Called Calendario)

A daily file (Called F0911), in this file i have a monthly amount

Then I created this measure to Accumulate the amounts:

This is the Measure that i use for Accumulate the amounts:

Acum_Detail =

VAR _fechaSel =SELECTEDVALUE(F0911[GL_DATE])

VAR _Jan01  = DATE(SELECTEDVALUE(Calendario[Year]),01,01)

VAR _DailyAcum=

CALCULATE(

SUM(F0911[AMOUNT]),

FILTER(ALL(Calendario[Date]), Calendario[Date]>=_Jan01 && Calendario[Date] <= _fechaSel))

RETURN

_DailyAcum

[saud968]

Try this

Create a Measure for Accumulated Amount:

Create a measure to calculate the accumulated amount by day.
Accumulated Amount =
CALCULATE(
SUM('Table'[Daily Amount]),
FILTER(
ALL('Table'),
'Table'[Date] <= MAX('Table'[Date])
)
)
Create a Measure for Monthly Average:

Create a measure to calculate the average accumulated amount by month, considering the filtered data.
Monthly Average Accumulated Amount =
VAR FilteredDays = COUNTROWS(VALUES('Table'[Date]))
VAR TotalAccumulated = SUM('Table'[Accumulated Amount])
RETURN
IF(
FilteredDays > 0,
TotalAccumulated / FilteredDays,
BLANK()
)
Create a Visual:

Add a visual (e.g., a table or a line chart) to display the monthly average accumulated amount.
Use the Month column on the axis and the Monthly Average Accumulated Amount measure as the value.
Apply Filters:

Add slicers or filters to allow users to select specific days (e.g., only Tuesdays).
The measure will automatically update based on the selected filters.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[divyed]

Hello @gomezc73 ,

Please share data in excel format removing sensitive data of any .

Cheers

[gomezc73]

Thank you for your earle response,

this is the daily data for Jan/Feb 2023.

Year	QT	Month	Week#	Week Day	Date	Beg. Balance	Daily Amount	Accumulated Amount
2023	Q1	Jan	1	Sun	01/01/2023	(3,633,363,058.57)	(200,793,352.34)	(3,834,156,410.91)
2023	Q1	Jan	1	Mon	01/02/2023	(3,633,363,058.57)	(160,984,809.71)	(3,794,347,868.28)
2023	Q1	Jan	1	Tue	01/03/2023	(3,633,363,058.57)	(171,002,732.78)	(3,804,365,791.35)
2023	Q1	Jan	1	Wed	01/04/2023	(3,633,363,058.57)	(187,864,203.54)	(3,821,227,262.11)
2023	Q1	Jan	1	Thu	01/05/2023	(3,633,363,058.57)	(203,681,447.59)	(3,837,044,506.16)
2023	Q1	Jan	1	Fri	01/06/2023	(3,633,363,058.57)	(183,632,474.30)	(3,816,995,532.87)
2023	Q1	Jan	1	Sat	01/07/2023	(3,633,363,058.57)	(151,498,500.22)	(3,784,861,558.79)
2023	Q1	Jan	2	Sun	01/08/2023	(3,633,363,058.57)	(154,679,706.85)	(3,788,042,765.42)
2023	Q1	Jan	2	Mon	01/09/2023	(3,633,363,058.57)	(174,369,516.83)	(3,807,732,575.40)
2023	Q1	Jan	2	Tue	01/10/2023	(3,633,363,058.57)	(206,220,918.69)	(3,839,583,977.26)
2023	Q1	Jan	2	Wed	01/11/2023	(3,633,363,058.57)	(236,211,164.03)	(3,869,574,222.60)
2023	Q1	Jan	2	Thu	01/12/2023	(3,633,363,058.57)	(258,153,570.51)	(3,891,516,629.08)
2023	Q1	Jan	2	Fri	01/13/2023	(3,633,363,058.57)	(251,865,102.92)	(3,885,228,161.49)
2023	Q1	Jan	2	Sat	01/14/2023	(3,633,363,058.57)	(233,377,305.04)	(3,866,740,363.61)
2023	Q1	Jan	3	Sun	01/15/2023	(3,633,363,058.57)	(208,237,287.73)	(3,841,600,346.30)
2023	Q1	Jan	3	Mon	01/16/2023	(3,633,363,058.57)	(232,621,139.40)	(3,865,984,197.97)
2023	Q1	Jan	3	Tue	01/17/2023	(3,633,363,058.57)	(266,672,453.96)	(3,900,035,512.53)
2023	Q1	Jan	3	Wed	01/18/2023	(3,633,363,058.57)	(299,258,224.42)	(3,932,621,282.99)
2023	Q1	Jan	3	Thu	01/19/2023	(3,633,363,058.57)	(335,272,651.29)	(3,968,635,709.86)
2023	Q1	Jan	3	Fri	01/20/2023	(3,633,363,058.57)	(349,768,788.13)	(3,983,131,846.70)
2023	Q1	Jan	3	Sat	01/21/2023	(3,633,363,058.57)	(343,437,136.23)	(3,976,800,194.80)
2023	Q1	Jan	4	Sun	01/22/2023	(3,633,363,058.57)	(327,954,547.86)	(3,961,317,606.43)
2023	Q1	Jan	4	Mon	01/23/2023	(3,633,363,058.57)	(341,233,603.36)	(3,974,596,661.93)
2023	Q1	Jan	4	Tue	01/24/2023	(3,633,363,058.57)	(357,264,773.29)	(3,990,627,831.86)
2023	Q1	Jan	4	Wed	01/25/2023	(3,633,363,058.57)	(387,481,162.91)	(4,020,844,221.48)
2023	Q1	Jan	4	Thu	01/26/2023	(3,633,363,058.57)	(401,421,287.46)	(4,034,784,346.03)
2023	Q1	Jan	4	Fri	01/27/2023	(3,633,363,058.57)	(409,091,519.76)	(4,042,454,578.33)
2023	Q1	Jan	4	Sat	01/28/2023	(3,633,363,058.57)	(397,470,111.77)	(4,030,833,170.34)
2023	Q1	Jan	5	Sun	01/29/2023	(3,633,363,058.57)	(372,610,512.51)	(4,005,973,571.08)
2023	Q1	Jan	5	Mon	01/30/2023	(3,633,363,058.57)	(383,348,709.47)	(4,016,711,768.04)
2023	Q1	Jan	5	Tue	01/31/2023	(3,633,363,058.57)	(220,729,172.89)	(3,854,092,231.46)
2023	Q1	Feb	5	Wed	02/01/2023	(3,633,363,058.57)	(437,990,270.92)	(4,071,353,329.49)
2023	Q1	Feb	5	Thu	02/02/2023	(3,633,363,058.57)	(467,710,764.13)	(4,101,073,822.70)
2023	Q1	Feb	5	Fri	02/03/2023	(3,633,363,058.57)	(474,584,309.43)	(4,107,947,368.00)
2023	Q1	Feb	5	Sat	02/04/2023	(3,633,363,058.57)	(462,276,357.37)	(4,095,639,415.94)
2023	Q1	Feb	6	Sun	02/05/2023	(3,633,363,058.57)	(447,085,664.68)	(4,080,448,723.25)
2023	Q1	Feb	6	Mon	02/06/2023	(3,633,363,058.57)	(463,843,973.76)	(4,097,207,032.33)
2023	Q1	Feb	6	Tue	02/07/2023	(3,633,363,058.57)	(485,377,556.93)	(4,118,740,615.50)
2023	Q1	Feb	6	Wed	02/08/2023	(3,633,363,058.57)	(514,364,746.89)	(4,147,727,805.46)
2023	Q1	Feb	6	Thu	02/09/2023	(3,633,363,058.57)	(528,997,690.50)	(4,162,360,749.07)
2023	Q1	Feb	6	Fri	02/10/2023	(3,633,363,058.57)	(556,233,576.50)	(4,189,596,635.07)
2023	Q1	Feb	6	Sat	02/11/2023	(3,633,363,058.57)	(541,522,262.93)	(4,174,885,321.50)
2023	Q1	Feb	7	Sun	02/12/2023	(3,633,363,058.57)	(508,599,533.49)	(4,141,962,592.06)
2023	Q1	Feb	7	Mon	02/13/2023	(3,633,363,058.57)	(521,117,133.42)	(4,154,480,191.99)
2023	Q1	Feb	7	Tue	02/14/2023	(3,633,363,058.57)	(550,096,607.34)	(4,183,459,665.91)
2023	Q1	Feb	7	Wed	02/15/2023	(3,633,363,058.57)	(577,518,344.42)	(4,210,881,402.99)
2023	Q1	Feb	7	Thu	02/16/2023	(3,633,363,058.57)	(602,029,788.49)	(4,235,392,847.06)
2023	Q1	Feb	7	Fri	02/17/2023	(3,633,363,058.57)	(624,044,082.24)	(4,257,407,140.81)
2023	Q1	Feb	7	Sat	02/18/2023	(3,633,363,058.57)	(610,344,120.33)	(4,243,707,178.90)
2023	Q1	Feb	8	Sun	02/19/2023	(3,633,363,058.57)	(582,228,014.87)	(4,215,591,073.44)
2023	Q1	Feb	8	Mon	02/20/2023	(3,633,363,058.57)	(606,782,402.39)	(4,240,145,460.96)
2023	Q1	Feb	8	Tue	02/21/2023	(3,633,363,058.57)	(634,502,523.92)	(4,267,865,582.49)
2023	Q1	Feb	8	Wed	02/22/2023	(3,633,363,058.57)	(668,756,145.05)	(4,302,119,203.62)
2023	Q1	Feb	8	Thu	02/23/2023	(3,633,363,058.57)	(693,499,600.23)	(4,326,862,658.80)
2023	Q1	Feb	8	Fri	02/24/2023	(3,633,363,058.57)	(707,633,820.40)	(4,340,996,878.97)
2023	Q1	Feb	8	Sat	02/25/2023	(3,633,363,058.57)	(694,673,124.90)	(4,328,036,183.47)
2023	Q1	Feb	9	Sun	02/26/2023	(3,633,363,058.57)	(664,142,827.66)	(4,297,505,886.23)
2023	Q1	Feb	9	Mon	02/27/2023	(3,633,363,058.57)	(682,582,459.17)	(4,315,945,517.74)
2023	Q1	Feb	9	Tue	02/28/2023	(3,633,363,058.57)	(523,876,044.55)	(4,157,239,103.12)

[divyed]

Hello @gomezc73 ,

Try below dax pls, replace table names and values accordingly.

Average_Total_Balance_Per_Month =
AVERAGEX(
SUMMARIZE(
'YourTable',
'YourTable'[Year],
'YourTable'[Month],
"Monthly_Total_Balance",
SUM('YourTable'[Begining_Balance] + 'YourTable'[Daily_Balance])
),
[Monthly_Total_Balance]
)

Did I answer your query ? Mark this as solution if this helps. Kudos are appreciated.

Cheers

[gomezc73]

Hi,

  The 'Beggining Balance' and 'Monthly Balance'  are MEASURES VARIABLES, not COLUMNS field, so when I use the SUM function it tells me it "The SUM function only accepts a column reference as an argument".