cancel
Showing results for
Did you mean: New Member

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

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.

1 ACCEPTED SOLUTION  Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4  Community Support

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  Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com  Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."  Solution Sage

Hi,

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