cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alonsocopetec
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.

 

alonsocopetec_1-1660419950696.png

 

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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
v-kalyj-msft
Community Support
Community Support

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
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
danextian
Super User
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."
www.linkedin.com/in/danebelarminocpa
MahyarTF
Solution Sage
Solution Sage

Hi,

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors