Fixed costs in BI

[ruthhacche]

I still have not solved this. I have these tables

The fixed costs are one line per month - both $500. The warehouse table is the big one with many lines per order but each order is only in one month. The orders is one line per order. So I have from the warehouse a) many months going to one month in the warehouse fixed and b) many orders going to one order in the orders table. I need to allocate the fixed costs to month, account, order - pro-rata by sales.

I can get the direct costs per month per order But how do I get the fixed cost to allocate as a percentage of sales. It should be $1,000 across all sales in 201908 and 201909 but if I just select 201908 it should be $500 pro-rated across just the sales for that month. Have I linked my tables wrong or am I missing a measure to do this?

 

[Matt Allington]

the bad news is you are a very long way off getting this working. You need to understand how Filter propagation works and how to build a dimensional model. The arrows on the relationships are important. I suggest read through my knowledge base here Knowledge Base - Excelerator BI

particularly this The Optimal Shape for Power BI Data - Excelerator BI

and this Relationships in Power BI and Power Pivot - Excelerator BI

 

[ruthhacche]

Thanks Mark - I realised this after posting. have reshaped the whole thing and now have it working through the filters properly. Only trouble I am having is with totals on my tables but i am confident I can get there now. Sorry - should have taken this down. Will also read your docs too - there is still lots to learn.