cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Loop SUMX and AVERAGEX

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]

1 ACCEPTED SOLUTION
Helper V

Thank you @Greg_Deckler , some how it didnt work.

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

``````VAR table1 =

SUMMARIZE(
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])
``````
2 REPLIES 2
Super User

@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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

Thank you @Greg_Deckler , some how it didnt work.

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

``````VAR table1 =

SUMMARIZE(
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])
``````

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors