topic Re: Loop SUMX and AVERAGEX in DAX Commands and Tips

Loop SUMX and AVERAGEX

amaniramahi — Sat, 16 Sep 2023 08:51:46 GMT

I have a table "AbandonedCallsDetails" contains the following columns:

CallerID	CallDate	Start of Hour	Month

I want to create a column to calculate the sum of monthly averages of the received calls

First thing is did is to summarizecolumns

SUMMARIZECOLUMNS( AbandonedCallsDetails[CallDate], AbandonedCallsDetails[Start of Hour], AbandonedCallsDetails[Month], "Count",COUNTROWS(AbandonedCallsDetails) )

I dont know how to do the remaining steps

I want to calculate the average of calls received in a certain [Start of Hour] per month

then to sum the averages per [Start of Hour]

Re: Loop SUMX and AVERAGEX

Greg_Deckler — Sat, 16 Sep 2023 13:26:48 GMT

amaniramahi Seems like the next step would be:

Table = VAR __Table = SUMMARIZECOLUMNS( AbandonedCallsDetails[CallDate], AbandonedCallsDetails[Start of Hour], AbandonedCallsDetails[Month], "Count",COUNTROWS(AbandonedCallsDetails) ) VAR __Table1 = SUMMARIZE( __Table, [Start of Hour], "Average", AVERAGE( [Count] ) ) RETURN __Table1

Then you could base a visual on this table and do a simple SUM on the Average column?

Re: Loop SUMX and AVERAGEX

amaniramahi — Sun, 17 Sep 2023 06:25:52 GMT

Thank you Greg_Deckler , some how it didnt work.

but I tried more complicated code, but at least it worked

VAR table1 = SUMMARIZE( ADDCOLUMNS( SUMMARIZE( AbandonedCallsDetails, AbandonedCallsDetails[CallTime], AbandonedCallsDetails[Month], "AverageCount", VAR CurrentCallTime = AbandonedCallsDetails[CallTime] VAR CurrentMonth = AbandonedCallsDetails[Month] RETURN AVERAGEX( FILTER( SUMMARIZE( AbandonedCallsDetails, AbandonedCallsDetails[CallDate], AbandonedCallsDetails[CallTime], AbandonedCallsDetails[Month], "Count", COUNTROWS(AbandonedCallsDetails) ), [CallTime] = CurrentCallTime && [Month] = CurrentMonth ), [Count] ) ), "SumOfAverages", SUMX( FILTER( SUMMARIZE( AbandonedCallsDetails, AbandonedCallsDetails[CallTime], AbandonedCallsDetails[Month], "AverageCount", VAR CurrentCallTime = AbandonedCallsDetails[CallTime] VAR CurrentMonth = AbandonedCallsDetails[Month] RETURN AVERAGEX( FILTER( SUMMARIZE( AbandonedCallsDetails, AbandonedCallsDetails[CallDate], AbandonedCallsDetails[CallTime], AbandonedCallsDetails[Month], "Count", COUNTROWS(AbandonedCallsDetails) ), [CallTime] = CurrentCallTime && [Month] = CurrentMonth ), [Count] ) ), [CallTime] = EARLIER(AbandonedCallsDetails[CallTime]) ), [AverageCount] ) ), AbandonedCallsDetails[CallTime], [SumOfAverages] ) RETURN SUMX(table1, [SumOfAverages])