Search code examples
powerbidaxpowerquerypowerbi-desktopm

How do I find average sales per week for a retail outlet category when there are multiple outlet data and categories every week in PowerBI?

In this example, I have output per week for clothing and footwear outlets (I have a a large database with multiple outlets and data spanning over 50,000 lines). In week 1 and week 2, I have only sales from Clothing-1 Outlet. I open a new clothing outlet in week 3. In week 4, I open my first footwear outlet. In week 6, my 3rd clothing outlet opens and in week 7 my 2nd footwear outlet opens. Due to financial reasons, I had to shut my 2nd clothing outlet in week 8.

Week Outlet Sales
1 Clothing-1 9
1 Clothing-1 5
2 Clothing-1 4
2 Clothing-1 5
2 Clothing-1 6
3 Clothing-1 10
3 Clothing-1 6
3 Clothing-2 4
3 Clothing-2 7
3 Clothing-2 6
4 Clothing-1 2
4 Clothing-2 10
4 Clothing-2 14
4 Footwear-1 1
4 Footwear-1 3
4 Footwear-1 2
4 Footwear-1 3
5 Clothing-1 5
5 Clothing-1 3
5 Clothing-1 2
5 Clothing-2 3
5 Clothing-2 6
5 Clothing-2 7
5 Clothing-2 7
5 Clothing-2 9
5 Footwear-1 2
5 Footwear-1 11
6 Clothing-1 4
6 Clothing-2 8
6 Clothing-3 8
6 Clothing-3 5
6 Footwear-1 3
7 Clothing-1 10
7 Clothing-1 4
7 Clothing-2 6
7 Clothing-2 8
7 Clothing-3 6
7 Footwear-1 9
7 Footwear-2 6
7 Footwear-2 5
7 Footwear-2 4
8 Clothing-1 9
8 Clothing-3 5
8 Clothing-3 5
8 Footwear-1 4
8 Footwear-1 4
8 Footwear-2 9
9 Clothing-1 9
9 Clothing-3 5
9 Footwear-1 4
9 Footwear-2 9

I need to find the effective and total sales week on week per clothing and footwear outlets. For eg, in week 6, I have a total of 25 sales in clothing over three outlets. My effective sales that week in clothing is hence 25/3 = 8.33 per outlet. In week 8, my second clothing outlet closes, so my effective sales in clothing that week is 19/2 = 9.5 per outlet. In the same week, my effective sales in footwear is 17/2 = 8.5.

I was hoping to find a DAX code in powerBI that might help with the analysis.

I tried analysing the data in excel but it takes forever to compute each category. In some cases, I have almost 14 outlets which open and close in different weeks so I really can't figure out how to proceed.

Additional Question: Now, if in week 4, clothing-1 had no sales but were open, I wouldn't have a row for clothing-1 in week 4. I know the store isn't shut down because they have sales in the ensuing weeks. If I recalculate based on this logic, I should have a clothing total sales of 24 in week 4 and my average sales would be 12 despite only clothing-2 making sales. Can this somehow be incorporated too?

Solution

  • I'm more familiar with Power Query M code where is pretty simple:

    In the Advanced Editor:

    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"

    enter image description here

    To do this all in DAX, you may try:

    • Add a column to your original Table (named SalesTable
        Type = LEFT([Outlet],FIND("-",[Outlet])-1)

    Then, create a new table:

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

    enter image description here

    Edit:
    Add zero sales entries if there is a gap for a particular store

    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"