Forum Discussion
time format average
Hi!, I have a column in table1 , like this: then I have the same column name and format in 2 different tables : Table2 and Table3 . I need the average of these 3 columns : (table1[avg.Sessio...
Hi,
Table1AvgTime = AVERAGEX(Table1, Table1[Avg. Session Duration]) Table2AvgTime = AVERAGEX(Table2, Table2[Avg. Session Duration]) Table3AvgTime = AVERAGEX(Table3, Table3[Avg. Session Duration]) TotalAvg = DIVIDE(Table1AvgTime + Table2AvgTime + Table3AvgTime, 3)It would help if you showed the structure of the tabels and model. Ideally you should have dimension tables for fields common to all tables, which would make the calculation straightforward. Having said that, you can try the following measure:
Average Time = VAR _T1 = SELECTCOLUMNS('Table 1', "Duration", 'Table 1'[Duration 1]) VAR _T2 = SELECTCOLUMNS('Table 2', "Duration", 'Table 2'[Duration 2]) VAR _T3 = SELECTCOLUMNS('Table 3', "Duration", 'Table 3'[Duration 3]) VAR _FinalTable = UNION(_T1, _T2, _T3) RETURN CONVERT(AVERAGEX(_FinalTable, [Duration]), DATETIME)Sample file attached
EDIT: I just tested lukiz84 approach and it delivers the same answer, and is of course much simpler!
Average by sum = VAR _T1 = AVERAGE('Table 1'[Duration 1]) VAR _T2 = AVERAGE('Table 2'[Duration 2]) VAR _T3 = AVERAGE('Table 3'[Duration 3]) RETURN CONVERT(DIVIDE(_T1 + _T2 + _T3, 3), DATETIME)
It would help if you showed the structure of the tabels and model. Ideally you should have dimension tables for fields common to all tables, which would make the calculation straightforward. Having said that, you can try the following measure:
Average Time =
VAR _T1 = SELECTCOLUMNS('Table 1', "Duration", 'Table 1'[Duration 1])
VAR _T2 = SELECTCOLUMNS('Table 2', "Duration", 'Table 2'[Duration 2])
VAR _T3 = SELECTCOLUMNS('Table 3', "Duration", 'Table 3'[Duration 3])
VAR _FinalTable = UNION(_T1, _T2, _T3)
RETURN
CONVERT(AVERAGEX(_FinalTable, [Duration]), DATETIME)
Sample file attached
EDIT: I just tested lukiz84 approach and it delivers the same answer, and is of course much simpler!
Average by sum =
VAR _T1 = AVERAGE('Table 1'[Duration 1])
VAR _T2 = AVERAGE('Table 2'[Duration 2])
VAR _T3 = AVERAGE('Table 3'[Duration 3])
RETURN
CONVERT(DIVIDE(_T1 + _T2 + _T3, 3), DATETIME)