Как найти средний объем продаж в неделю для категории торговых точек, если каждую неделю в PowerBI есть несколько данных и категорий торговых точек?

Я больше знаком с кодом Power Query M, где все довольно просто:

В расширенном редакторе:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldPBCsIwEATQf8m5gt2kbfYs+BOlhx5EBbEgBX9fbCAhmUnRYx/DZjtNx9G0pjGnx7Le7s/r4fugZmoIdxtLyY5zJd1vbEtuj9xJXOKZwAPnMMSVswVZ0iroLvp5Wdb3ZX6F1TlbzrKX7niJwJW0IAtPp1aAh79YI5O3BG5DVz2/Qn053CPbOneRSbND5cKBO+RUFrBHtlk6W0WRhaclvg9wWNDzf9bzUnY4W9D9zOnbK99E+ZHKZyuZPX0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Outlet = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Outlet", type text}, {"Sales", Int64.Type}}),
    
//add columnm to determine Footwear vs Clothing type
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Type", each Text.Split([Outlet],"-"){0}, type text),

//Group by Type and Week
//Then compute total and average per unique store
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Week", "Type"}, {
        {"Total Sales", each List.Sum([Sales]), type nullable number}, 
        {"Sales per Outlet", each List.Sum([Sales]) / List.Count(List.Distinct([Outlet])), type nullable number}})
in
    #"Grouped Rows"

Чтобы сделать все это в DAX, вы можете попробовать:

• Добавьте столбец в исходную таблицу (с именем SalesTable

    Type = LEFT([Outlet],FIND("-",[Outlet])-1)

Затем создайте новую таблицу:

SummaryTable = SUMMARIZE('SalesTable'  
      ,[Week] 
      , [Type]
      , "Total Sales", SUM('SalesTable'[Sales])
      , "Average Sales", SUM('SalesTable'[Sales]) / DISTINCTCOUNT('SalesTable'[Outlet])
      )  

Обновлено:
Добавьте нулевые записи о продажах, если для определенного магазина есть пробел.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldNBCsIwEAXQu2RdwUzSNrMWvETpwoWoIBak4PXFRhKa/1N02cdnZjJJh8FY05jDfZqvt8dl9/lQMzaE24WlZM+5ku4WdiXbPXcSl9QTuOcci/iSvz3RffLjNM2v8+kZZ+TsOMtWuuXbAq6kBVl4Oh8fuP+LNTE5JbCNu+r4W+nK4gHZ1blNTDbbV14WuEfOywIOyG6VXo2iyMLTks4DHAcM/OcMfCkbvBrQ/8z57pVPoryl8trKa1tymeMb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Outlet = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Outlet", type text}, {"Sales", type number}}),
   
//Find missing weeks and create a zero(0) sales record for those weeks
    #"Grouped Outlet" = Table.Group(#"Changed Type", {"Outlet"}, {
        {"Missing Weeks", (t)=>
            let 
                #"Missing" = List.RemoveMatchingItems({List.Min(t[Week])..List.Max(t[Week])}, t[Week]),
                #"Records"= List.Accumulate(
                    #"Missing",
                    {},
                    (s,c)=> s & {[Outlet=t[Outlet]{0}, Week=c, Sales=0]}
                )
            in Table.FromRecords(#"Records"), type table[Outlet=text, Week=Int64.Type, Sales=number]}}),
    #"Expanded Missing Weeks" = Table.ExpandTableColumn(#"Grouped Outlet", "Missing Weeks", {"Week", "Sales"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Missing Weeks", each ([Week] <> null)),

//Combine missing weeks with original table
    #"Combine" = Table.Combine({#"Changed Type", #"Filtered Rows"}),
    #"Filtered Rows1" = Table.SelectRows(Combine, each true),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Week", Order.Ascending}, {"Outlet", Order.Ascending}}),

//add columnm to determine Footwear vs Clothing type
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Type", each Text.Split([Outlet],"-"){0}, type text),

//Group by Type and Week
//Then compute total and average per unique store
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Week", "Type"}, {
        {"Total Sales", each List.Sum([Sales]), type nullable number}, 
        {"Sales per Outlet", each List.Sum([Sales]) / List.Count(List.Distinct([Outlet])), type nullable number}})
in
    #"Grouped Rows"