Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
AlB
Super User
Super User

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:

 

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.

 

Anonymous
Not applicable

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, 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.