Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |