Weighet Average Based On Previous Row with additional conditions

[andrezmor]

I have a table that I am naming “Transaction_TB” where each row corresponds to a transaction for inputs and outputs of coal tonnage in an inventory system, the table has 4 columns

[Rank] = Unique ID that can be used to sort records in chronological order

[Type] = Type of Transaction (IN or OUT)

[Tons] = amount of ton that are being moved, OUT Type transactions are negative

[Quality]= Only Type IN transaction have quality

I have calculated the BALANCE_TONS after each transaction which is simply add or subtract IN Type transaction minus OUT Type transactions, no problem with that, however my problem is calculating the BALANCE_QUALITY

** BALANCE_QUALITY has to be calculated as weighted average over the Tons

** Only the Type IN transaction affect the quality, so for Type OUT transactions the Quality is the same as previous transaction.

The expected result would be

I appreciate your help and suggestion on how to solve the problem.

Thanks

[ryan_mayu]

@andrezmor 

how you get the balance qty? could you pls provide more details?

[andrezmor]

Hi @ryan_mayu 

For Rank # 2 it would be:

For Rank # 3
                Quality is the same as previous BALANCE_QUALITY only BALANCE_TONS is affected.

For Rank # 4

Thanks

[ryan_mayu]

@andrezmor 

balance_tons = sumx(FILTER('Table','Table'[Type]="IN"&&'Table'[Rank]<=EARLIER('Table'[Rank])),'Table'[Tons])+ sumx(FILTER('Table','Table'[Type]="OUT"&&'Table'[Rank]<=EARLIER('Table'[Rank])),'Table'[Tons])

the second column will cause circular reference. Let's see if anyone else can help you on this.