Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]
Solved! Go to 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])
@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?
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])
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
39 | |
22 | |
18 | |
15 | |
12 |