cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frivik Regular Visitor
Regular Visitor

Creating monthly sales reports

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! 

3 REPLIES 3
Super User
Super User

Re: Creating monthly sales reports

Hey @Frivik 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:

 

image.png

 

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.

 

Highlighted
Frivik Regular Visitor
Regular Visitor

Re: Creating monthly sales reports

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, 

Super User
Super User

Re: Creating monthly sales reports

@Frivik

 

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 Smiley Happy