summarize sales table to grouby product , month , year and percentage of monthly sales over yearly

[osama_ayoub]

I want to summarize sales table to show product , month , year and percentage of monthly sales over yearly sales ?

[FreemanZ]

hi @osama_ayoub 

tried to get a solution based on a simplified dataset like:

1) add a column like:

YYYYMM = FORMAT([Date], "YYYYMM")

2) plot the product, YYYYMM columns with a measure like:

Yearly% = 
VAR _a = SUM(TableName[Sales])
VAR _b =
CALCULATE(
    SUM(TableName[Sales]),
    LEFT(TableName[YYYYMM],4) = LEFT(MAX(TableName[YYYYMM]),4)
)
RETURN
DIVIDE(_a, _b)

or

Yearly%2 = 
VAR _a = SUM(TableName[Sales])
VAR _b =
CALCULATE(
    SUM(TableName[Sales]),
    YEAR(TableName[Date]) = YEAR(MAX(TableName[Date])),
    ALLSELECTED(TableName[YYYYMM])
)
RETURN
DIVIDE(_a, _b)

it worked like this:

[osama_ayoub]

Thanks for your help,

I want to create new table in the data model because I will need this table to connet it to Model 

I need this table to summarize sales table to show product and month and year and sales for each month and percentage of monthly sales of each product over the  yearly sales for only each product not all prosucts.

[Greg_Deckler]

@osama_ayoub First, add a Year column:

Year = YEAR([Date])

If you want a summarized table, then you can do this:

Table = 
  VAR __Table = 
    SUMMARIZE(
      'Sales',
      [Product ID], [Year], [Month],
      "__TotalPriceSales", SUM('Sales'[Total Price Sales])
    )
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__YearSales", SUMX(FILTER(__Table,[Year] = EARLIER([Year])),[__TotalPriceSales])
    )
  VAR __Table2 = 
    ADDCOLUMNS(
      __Table1,
      "__YearlyPercent", DIVIDE([__TotalPriceSales], [__YearSales])
    )
RETURN
  __Table2
    

[osama_ayoub]

I want to divide the monthly sales for each product over the yearly sales for only this product ,So the denominator is the yearly sales for one product .

Thanks for your help @Greg_Deckler 

[Greg_Deckler · Accepted solution]

@osama_ayoub So:

Table = 
  VAR __Table = 
    SUMMARIZE(
      'Sales',
      [Product ID], [Year], [Month],
      "__TotalPriceSales", SUM('Sales'[Total Price Sales])
    )
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__YearSales", SUMX(FILTER(__Table,[Year] = EARLIER([Year]) && [Product ID] = EARLIER([Product ID])),[__TotalPriceSales])
    )
  VAR __Table2 = 
    ADDCOLUMNS(
      __Table1,
      "__YearlyPercent", DIVIDE([__TotalPriceSales], [__YearSales])
    )
RETURN
  __Table2