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