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!
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.
Super UserHey 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!