cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## New Column in Table with sum of sales by month

Ok, I'm struggling with something which seems like it should be straight forward.

My end goal is to have a measure which distributes the monthly fixed cost of a supplier by the various products, based on % of sales in that month.

In my mind, the easiest way to do that was to create a new summary table, which has date, product & sales.
Then I was planning to create a new column with Monthly Sales, where for each row it would have the total sales for that month. Then 2 more columns showing the % of total sales and based on that percentage, the distributed cost.

Something like this, where I'm taking a fixed cost of £1,000 per month:

 CloseDate Product Sales Monthly Sales % of Monthly Sales Distributed Costs 05/01/2020 Product A £5,000 £10,000 50% £500 08/01/2020 Product A £1,000 £10,000 10% £100 10/01/2020 Product B £4,000 £10,000 40% £400

In my mind, this should do the trick - but it's showing the total amount of sales in the table, not filtered by month:

``````Monthly Sales =

SUMX(
FILTER('TableName',
'TableName'[CloseDate]>EOMONTH('TableName'[CloseDate],-1)&&
'TableName'[CloseDate]<=EOMONTH('TableName'[CloseDate],0)),

'TableName'[Sales])``````

Any ideas?

Thanks,
Patrick

6 REPLIES 6
Community Support

@patrick3

Try create a summarize table: group by [date].[month], something like:

``Table 2 = SUMMARIZE('Table',[CloseDate].[Year],'Table'[CloseDate].[Month],'Table'[Product],"month total",SUM('Table'[Sales]))``

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helper II

Hi Paul,

Sorry if I've not been clear, but I'm ok in creating the summarized table.

I'm having trouble with the Monthly Sales column.

Thanks,

Patrick

Community Support

The new column is included in my Summarize table function:

Table 2 = SUMMARIZE('Table',[CloseDate].[Year],'Table'[CloseDate].[Month],'Table'[Product],"Monthly Sales",SUM('Table'[Sales]))

Paul

Helper II

Hi Paul,

Ah I see, sorry.

That makes sense and would work, but ideally I'd like to have daily numbers in this table as well.

Mainly so that during the month, it's already distributing the costs so far...so my table has daily rows with sales and various products, which means I need a formula to sum up the monthly sales based on the date in that particular row.

Thanks,

Patrick

Super User

@patrick3, where do fixed costs come from? Is it the same for each supplier? You have to provide a little more context of the data to get an effective solution.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

Hi @parry2k in this case there's only one supplier, so the fix costs could be quite literally hard coded into the final column, or alternatively I could feed it in using a related table later on if we do get more instances of this.

However for the current purpose, you can assume this is an exception and it will be hardcoded, just for this one supplier.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors