Split value according to the number of times a company is repeated

[alonsocopetec]

Hello.  I split a column into several comma delimited values and then performed an unpivot to convert them into rows assigned to each company. But considering that the total value of the investment is the sum of the 3 or 4 investors together, I would like to make a division of this value by the number of times the company is repeated.

For example:
ByteDance repeats 4 times, then the valuation of 140B is divided by 4 getting 35B.
In the case of SpaceX it is repeats 3 times then 127/3 = 42.33 for each row.

[v-yanjiang-msft]

Hi @alonsocopetec ,

Does @Ashish_Mathur 's solution solve you problem? If so, would you mind accept the reply as solution? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[Ashish_Mathur · Accepted solution]

Hi,

I would prefer to perform this calculation as a calculated column formula in DAX rather than in the Query Editor (though this can also be done in the Query Editor).  Try this calculated column formula

Count = calculate(max(Data[N-investor]),filter(Data,Data[Company]=earlier(Data[Company])))

Val per investor = divide(Data[Valuation ($B)],Data[Count])

Hope this helps.

[danextian]

Please post a sample data that we can copy paste or easily manipulate as well as your expected result given the sample data.

[MahyarTF]

Hi,

Would you please share some sample data and also your expected result here to make a better sense?