Monthly fixed costs divided by transaction

[Naeven]

Hello,

I have a table of transactions with Kg and a transaction date.

I have fixed costs per month that I want to divide to every transaction of that specific month, weighted depending on the Kg SUM of each month & Kg of each transaction.

So I imagine that every transaction row should calculate something like this; Transaction_Fixed_Cost = Transaction_Kg * (Fixed_Monthly_Cost / SUM_Monthly_Kg)

The Fixed_Monthly_Cost would be a parameter.

But I'm having troubles trying to achieve this in PowerBi, specially the SUM_Monthly_Kg within every row, not sure if I should usa a meassure, calculated column, or something ells.

I hope it's enough info, any help would be appriciated.

[v-ljerr-msft]

Hi @Naeven,

If I understand you correctly, you should be able to use the formula below to create a new calculate column in your transactions table by firstly adding month column in transactions table, then create a relationship between these two tables with month column in both tables. 

Transaction_Fixed_Cost =
'TransactionsTable'[Transaction_Kg]
    * ( RELATED ( 'CostTable'[Fixed_Monthly_Cost] ) / 'Transactions'[SUM_Monthly_Kg] )

Regards

[Naeven]

Hi @v-ljerr-msft,

Sorry about the late reply. That got me a little bit further, but still I'm struggling on the part of SUM_Monthly_Kg. wich is not something I've already calculated/set up.

Currently I'm doing this;

Fixed_Cost = Transactions[Kg] * (10000 / SUM(Transactions[Kg]))

And now I whould like to add the part were the SUM(Transactions[Kg]) is per month that the transaction belongs to, let's say per "Transaction[Date]

[Naeven · Accepted solution]

OK, I managed to google a solution now. Cheers for your help

Fixed_Cost = Transactions[kg] * (10000 / CALCULATE(SUM(Transactions[kg]);ALLEXCEPT(Transactions;Transactions[Date].[Month])))