Forum Discussion
Cost Allocation with three tables
Hello all,
I'm currently facing a complication that I can't solve and I'm kindly asking you to help me.
I have three tables: clients table, sales table, and overheads table. The sales and overheads tables are linked to the clients table by the analytical code column. Additionally, in the clients table, I have a "Overhead" client, whose total should be allocated among the other clients. I have already created the measures for total revenue and the % revenue per client. However, I am struggling to allocate the overheads.
I need to allocate the overheads proportionally to each client's revenue. Could you please help me with this?
Here is a brief overview of my tables :
1. Client table
2. Sales table
3. Overheads table
Thank you in advance.
Best regards.
Hey Victor_YU ,
Maybe you can try something like that:
AllocatedOverhead =
CALCULATE(
SUM('Overheads'[Amount]) *
DIVIDE(
SUM('Sales'[Sales]),
CALCULATE(SUM('Sales'[Sales]), ALL('Client'))
),
TREATAS(VALUES('Client'[Client Name]), 'Sales'[Client Name])
)
We use ALL('Client') to remove the filter from the Client table to calculate the total sales across all clients for proportional allocation, and TREATAS ensures that the relationship between the Client table and the Sales table is respected when filtering.
Let me know if this works for your scenario!
6 Replies
- marcelsmaglhaes
Super User
Hey Victor_YU
Overhead =
CALCULATE(
SUM('Overheads'[Amount]) * [YOUR_PERCENTAGE_MEASURE],
'Clients'[Category] = "Sales"
)
TheAllocatedOverheadmeasure calculates the overhead amount allocated to each client based on their revenue percentage. It multiplies the total overhead amount by the revenue percentage for each client, ensuring that the allocation is applied only to clients in the "Sales" category, excluding any clients that are categorized differently, such as "Overheads.Thank you for your prompt reply marcelsmaglhaes, it works. But I made a mistake in the screenshots. The 'Category' column (with sales and overheads) is in the Sales table and not in the Client table. The formula you provided works well, but only with the 'Client Name' from the Sales table. It doesn’t work with the field from the Client table, and since I'm using this table in all my visuals, it's a bit problematic for me. Thanks in advance for your help.
- marcelsmaglhaes
Hey Victor_YU ,
Maybe you can try something like that:
AllocatedOverhead =
CALCULATE(
SUM('Overheads'[Amount]) *
DIVIDE(
SUM('Sales'[Sales]),
CALCULATE(SUM('Sales'[Sales]), ALL('Client'))
),
TREATAS(VALUES('Client'[Client Name]), 'Sales'[Client Name])
)
We use ALL('Client') to remove the filter from the Client table to calculate the total sales across all clients for proportional allocation, and TREATAS ensures that the relationship between the Client table and the Sales table is respected when filtering.
Let me know if this works for your scenario!
Hi Victor_YU ,
Regarding your question, the 'Alpha' is calculated like this?
(400 + 100 + 120 + 80) * (600 / 2200)
Correct me if I'm misunderstanding.
Best Regards,
Wenbin ZhouHi Anonymous .
January Sales amount is not calculated. It is a basic amount withount any calculation and my topic is related to overheads allocation to all clients based on their sales accordingly % of total sales amount. I am looking for this formula : total overheads amount * (customer sales amount/total sales amount). Hope that is clear enough.
Best regards.
Hi Victor_YU ,
Try this
Measure = CALCULATE(SUM('Overheads table'[Amount]),ALL('Client table')) * [PERCENTAGE]If there is still a problem, please provide the .pbix file without sensitive data.
If you are unsure how to upload data please refer to
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wenbin Zhou
