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: CallerID CallDate Start of Hour Month           I want to create a column to calculate the sum of monthly aver...
"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])
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])