Forum Discussion
Cost Allocation with three tables
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 Zhou
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.
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