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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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