Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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-yanjiang-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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
MahyarTF
Memorable Member
Memorable Member

Hi,

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

Mahyartf

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.