Cost Allocation with three tables

[Victor_YU]

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.

[Anonymous]

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 Zhou

[Victor_YU]

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

[Anonymous]

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

[marcelsmaglhaes]

Hey @Victor_YU 

Overhead =
CALCULATE(
SUM('Overheads'[Amount]) * [YOUR_PERCENTAGE_MEASURE],
'Clients'[Category] = "Sales"
)

The AllocatedOverhead measure 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.

[Victor_YU]

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 · Accepted solution]

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!