Divide sales revenue of one product on three other products based on portion of each of three

[msalari]

Hi,

I have 6 products, and want to asigne reveneu of one of them (product A) to three others(Product B, C, D). The asignment should be based on portion of reveneu of each of the three products. Meaning if 30% of total reveneu of B, C, D is for B, so 30% of total reveneu of A should be asigned to B.

And there is one table which include all transactions with amount and product names.

What I did, calculating a masure as:

Ratio_for_Devide = 1+(CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product] = "A")/(CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product]= "B")+CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product] = "C")+CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product] = "D")))

Then I multipl this ration in all lines in the table in a new column as:

Product CAD = if(Table[VP Product]="B",(Table[Normalized CAD]*[Ratio_for_Devide]),if(Table[VP Product]="C", (Table[Normalized CAD]*[Ratio_for_Devide]),if(Table[VP Product]="D", (Table[Normalized CAD]*[Ratio_for_Devide]),if(Table[VP Product]="A",0,Table[Normalized CAD]))))

Now, the value of the ratio is correct but when line by line it is multipe to amount, it is less than actual ratio?

How can I fix it?

[Eric_Zhang]

@msalari

Your requirement is not clear for me, would you mind posting some sample data and expected output?

Or even better you could upload the pbix file to any web storage, such as Onedrive, Dropbox and share a link.