How does one calculate the minimum & average by row over multiple columns in Power BI DAX

Question

I am having a hard time writing DAX to calculate the average of aggregated averages by row and column. I also, am not having luck writing DAX to calculate the minimum of the aggregated averages by row. This is how my data is structured:

These tables represent what the data looks like aggreated (Table 1) and what the desired output is (Table 2).

Can anyone help? Please let me know if you need further clarification.

Answer 1

Try the following three Measures:

Avg by Date = 
  AVERAGEX(
    DISTINCT('YourTable'[Department]),
    CALCULATE(AVERAGE(YourTable[Value]))
  )


Min Avg by Date = 
  MINX(
    DISTINCT('YourTable'[Department]),
    CALCULATE(AVERAGE(YourTable[Value]))
  )


Avg of Avg by Date = 
  CALCULATE(
    AVERAGEX(
      DISTINCT('YourTable'[Date]),
      [Avg by Date]
    ),
    ALLSELECTED('YourTable'[Date])
  )

Answer 2

FinalAverageByDate = 
VAR SummaryTable =
    SUMMARIZE(
        'YourTable', 
        [Date],
        [Department],
        "AverageValue", 
         AVERAGE([Value])
    )
RETURN
        SUMMARIZE(
            SummaryTable,
            [Date],
            "AverageValue", 
            AVERAGE([AverageValue])
        )
// Just create a new table with this code)

Answer 3

You can achieve this using Power Query

M code for this is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCsAgDATQu7guNH/1LNL7X6OSpIsG3AjyGCdxrYbtaqA32k1AvC8MsE8zas+1GlU25z7ZmQ/pGWlJ7h9LpHU4a2VKVmc7pG069+Tx7+4izqOyBndznpXzcQ5GSJ9lNozNEKtnO0M4HRxjeMyfIyj9mP1S/dsOw/XgtPufFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Date = _t, Department = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Date", type date}, {"Department", Int64.Type}, {"Value", Int64.Type}}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Department", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Department", type text}}, "en-US")[Department]), "Department", "Value", List.Sum),
    #"Grouped Rows" = Table.Group(#"Pivoted Column1", {"Date"}, {{"Dept200", each List.Sum([200]), type nullable number}, {"Dept300", each List.Sum([300]), type nullable number}, {"Dept400", each List.Sum([400]), type nullable number}, {"Dept500", each List.Sum([500]), type nullable number}, {"Dept600", each List.Sum([600]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Min({[Dept200],[Dept300],[Dept400],[Dept500],[Dept600]})),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "MinByDate"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Average({[Dept200],[Dept300],[Dept400],[Dept500],[Dept600]})),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Custom", "AvgByDate"}}),
    AvgofAvg= Table.AddColumn(#"Renamed Columns1", "Custom", each List.Average(#"Renamed Columns1"[AvgByDate])),
    #"Renamed Columns2" = Table.RenameColumns(AvgofAvg,{{"Custom", "AvgOfAvgByDate"}})
in
    #"Renamed Columns2"