New Column in Table with sum of sales by month

[patrick3]

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

[V-pazhen-msft]

@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

[patrick3]

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

[V-pazhen-msft]

@patrick3 

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

[patrick3]

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

[parry2k]

@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.

[patrick3]

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.