Allocated accounting

[Z7-852]

How do I do allocated accounting in Power BI?

I have one table that contains cost rows.

ID Product ID Cost

01	01	100
02	02	100

And second table that tells how costs should be allocated for each product

Product ID Cost center Allocation

01	01	0,5
01	02	0,5
02	02	1

Now for the problem that is so easy in excel but bit harder in Power BI.

I want to create measure that calculate cost per cost center. In this easy example it would yield cost of 50 for cost center 1 and 150 for cost center two.

[v-yulgu-msft]

Hi @Z7-852,

I have tested this scenario using the sample data you provided, while the result wasn't changed if I added page level filters or used date slicer to filter costs table.

In my test, I create one to many relationship between these two tables based on common field [Product ID].

Then, I used below formula to create calculated column in the second table.

Allocation Account = Allocation[Allocation]* RELATED(Costs[Cost])

You said filters didn't work with your calculated column, if possible, please share the screenshot about the incorrect output you have got. And please provide more detailed description about Correct Answer that you want to get after applying filters.

Thanks,
Yuliana Gu

[Z7-852]

Allocated cost data model

My data model is bit different. I take cost data over the product table and that seems to be the problem.

In the visuals data is calculated right as long as I don't filter costs table (blue in visual).

[Z7-852]

While working on the issue I have located my problem.

When using calculated column (allocation * sum('Costs'[Cost])) it gives me right answer. But if I have page level filters or try to use date slicer to filter costs table the calculated column doesn't give right answer.

So... How do I get my filters to work with calculated column.