Prorata over a table

[ruthhacche]

I have a table with sales per account filtered for August. I have a measure that calculates the direct costs = 175 and that splits correctly per account because it is derived per order per account anyway. I have another measure that calculates the fixed costs for August = 100 but that does not relate to an order or an account - I want to allocate it over each of the accounts - pro rata to the sales as below. Will be obvious to most I am sure.

 

[Saba Sabaratnam]

Hi

Enter your fixed costs away from your table by month as shown below.

Then enter the following formula in Fixed Costs field and copy it down

=C2/SUMIFS(C:C,A:A,A2)*VLOOKUP(A2,J:K,2,FALSE)

Aug

Sep

Kind regards

Saba

 

[ruthhacche]

HI Saba. Thank you for this. I am trying to do this in power BI not excel.