Creating monthly sales reports

Question

Hi everyone,&nbsp;I have a sheet with 4 different products (a,b,c,d), and month of purchase&nbsp;the different columns is aligned like this:&nbsp;[year][month][name of product][product A][Product B][Product C][Product D]&nbsp;Would it be possible to create a calculated column that summarizes sales per product per month?&nbsp;Each row only has one input. Needs to be filtering&nbsp; by the name of the product, month and year.&nbsp;&nbsp;Example:&nbsp;[year][month][name of product][product A][Product B][Product C][Product D]2019 &nbsp; Jan &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 90 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;2019 &nbsp; Feb &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 80 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;In advance, Thanks!&nbsp;

alb · Answer

Hey Anonymous whats up?&nbsp;You mean this?&nbsp;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:&nbsp;&nbsp;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:&nbsp;&nbsp;Table2 =
ALL ( Table1[year], Table1[month], Table1[name of product], Table1[NewColumn] )The two steps above could&nbsp;also be carried out in only one step combining ADDCOLUMNS and SELECTCOLUMNS&nbsp;All of this can also be done the query editor.&nbsp;

anonymous · Answer

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 · Answer

Anonymous&nbsp;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?&nbsp;I need an illustrative example so&nbsp;that we can proceed. A complete one :smileyhappy:

Forum Discussion

Creating monthly sales reports

3 Replies

Related content

Fabric Create report API

Monthly Automatic Change

Scheduling reports monthly

Monthly Count not working

Subscription to report weekly/monthly