Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
amaniramahi
Helper V
Helper V

Loop SUMX and AVERAGEX

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

CallerIDCallDateStart of HourMonth
    

 

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

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])

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
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?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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])

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors