Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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
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.
Please post a sample data that we can copy paste or easily manipulate as well as your expected result given the sample data.
Proud to be a Super User!
Hi,
Would you please share some sample data and also your expected result here to make a better sense?
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |