Skip to main content
cancel
Showing results for 
Search instead 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

Reply
patrick3
Helper II
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:

CloseDateProductSalesMonthly Sales% of Monthly SalesDistributed Costs
05/01/2020Product A£5,000£10,00050%£500
08/01/2020Product A£1,000£10,00010%£100
10/01/2020Product B£4,000£10,00040%£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
V-pazhen-msft
Community Support
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

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

@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

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

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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