Forum Discussion

amaniramahi's avatar
amaniramahi
Icon for Helper V rankHelper V
3 years ago
Solved

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]

"Need Help"
  • amaniramahi's avatar
    amaniramahi
    3 years ago

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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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?

     

    • amaniramahi's avatar
      amaniramahi
      Icon for Helper V rankHelper V
      3 years ago

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