common cost allocation based on Expenditure type and Asset category

Problem Statement:

Hey Folks,

We have 1 requirement of allocating common cost to specific asset assets based on the expenditure type of the cost and asset category mapped to asset.

Suppose -

Expenditure type - Sewage charges

then, that cost should be prorated on the assets which have the asset category as -

Major Minor

Building Draining

Building Sewer

Lease Draining

Lease Sewer

Lease Fabrication

Investment Draining

Investment Draining

Is there any way by which we can achieve this. We have tried with Value set DFF at expenditure type level, Common Lookups, Mapping sets but in every case the issue is same we cannnot repeat the expenditure type value but this scenario is a one to many relation.