Creating monthly sales reports

[Anonymous]

Hi everyone, 
I have a sheet with 4 different products (a,b,c,d), and month of purchase 

the different columns is aligned like this: 

[year][month][name of product][product A][Product B][Product C][Product D]

Would it be possible to create a calculated column that summarizes sales per product per month? 

Each row only has one input. Needs to be filtering  by the name of the product, month and year. 

Example: 

[year][month][name of product][product A][Product B][Product C][Product D]

2019   Jan           A                            90            0                 0                    0 
2019   Feb           B                            0              80               0                    0 

In advance, Thanks! 

[AlB]

Hey @Anonymous whats up?

You mean this?

NewColumn = Table1[productA]+Table1[ProductB]+Table1[ProductC]+Table1[ProductD] 

It assumes that the only non-zero column in each row is that of the product (as it happens in your sample data).

You'll get something like this:

You could later eliminate the 'Product A' ... 'Product D' columns since they would be redundant. For instance by creating a new calculated table based on the one above:

Table2 =
ALL ( Table1[year], Table1[month], Table1[name of product], Table1[NewColumn] )

The two steps above could also be carried out in only one step combining ADDCOLUMNS and SELECTCOLUMNS

All of this can also be done the query editor.

[Anonymous]

Hey AIB, all good here still learning, and you?

I agree that i could use that as a possible solution. However, the point of this column is to provide a monthly sales total per product so that I can compare that towards forecasts. I could easily do this with graphics by sorting per month, but i need to ceate a number so I can use it at a later stage. 

How would you approach it filtering by month, year and product? 

Cheers, 

[AlB]

@Anonymous

Why don't you show an example with sample data that covers all the cases that you require? That would help as I'm not sure I understand what you need.

Do you mean there are for Product A for instance several rows for Jan 2019 and you want to add them all?

I need an illustrative example so that we can proceed. A complete one