Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Z7-852
Helper I
Helper I

Allocated accounting

How do I do allocated accounting in Power BI?

 

I have one table that contains cost rows.

ID Product ID Cost

0101100
0202100

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

Product ID Cost center Allocation

01010,5
01020,5
02021

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.

3 REPLIES 3
v-yulgu-msft
Employee
Employee

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].
1.PNG

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

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

2.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Allocated cost data modelAllocated 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
Helper I
Helper I

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.