Forum Discussion
Single-cell stock allocations
I am looking for a solution preferably in DAX, or alternatively in M/Power Query.
My dataset consists of a number of investors' stock portfolios. The number of stocks in each portfolio varies across investors (e.g. one may invest in 2 stocks, another in 10), and so do their allocations towards each.
Each portfolio is stated in a single row in a single column (B), in the following format: Stock 1/5%:Stock 2/10%:Stock 3/85%. Of course the stocks have actual names, this is simply an example.
While the number of stocks in a portfolio is variable, the range of stocks investors can choose is fixed and can be categorized as either Europe, U.S., or Asia stocks.
Ultimately, what I want to calculate is the allocation towards each of these 3 regions (e.g. may Investor X have 10% in European stocks, 85% in American, and 5% in Asian). My current solution in Excel is to extract the stock names, lookup their region in a lookup table, and then sum the amounts in each.
However, my formula is not the most straight-forward, and I cannot seem to convert it to DAX, as I am now moving my calculations into PowerPivot. Here is the formula:
=SUMPRODUCT(TEXT(TRIM(MID(SUBSTITUTE($B4&$G$4:$G$15;"%";REPT(" ";100));FIND($G$4:$G$15;SUBSTITUTE($B4&$G$4:$G$15;"%";REPT(" ";100)))+LEN($G$4:$G$15&"/");100));"0,000;0;0;\0")*($H$4:$H$15="U.S."))/100
Unfortunately I have not been able to attach the sample workbook, but instead I have enclosed an image of the sheet below. Thanks a lot in advance, let me know if you need further.
15 Replies
camargos88Community Champion
Hi Anonymous ,
I believe that using power query you can split each stock in rows.
So you can relate the tables.
Can you paste the values here ?
Ricardo
I agree, but I am not sure whether it will work, as I do not know the number of stocks in each portfolio beforehand and hence neither whether to split across 5, 10, 20 columns etc.
Anyways, if you have a flexible solution, I'd really appreciate your input. Here is the sample data.
Investor 1 Stock 1/5%:Stock 2/10%:Stock 3/85% Investor 2 Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000 Investor 3 Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000 Investor 4 Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004 Investor 5 Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000 Investor 6 Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000 Investor 7 Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004 Investor 8 Stock 1/10%/1000:Stock 2/60%/6000:Stock 5/20%/2000:Stock 6/10%/1000 Investor 9 Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000 Investor 10 Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004 Investor 11 Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000 Investor 12 Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000
- camargos88
Hi Anonymous ,
Check this file: Download PBIX
Now just input the other tables and relate it.
If you consider it as a solution, please mark as a solution and kudos.
Ricardo
- camargos88
Hi Anonymous ,
I've updated the file with another table. This new code has another approach to handle it.
I hope it works.
Ricardo
Great, definitely works better.
One issue, however. As mentioned, I know the range of stocks available, however, they won't be called "stock 1", "stock 2" etc., but instead "Amazon", "Facebook", etc.
How I look at it, this step is going to be an issue. Any suggestions around this? I have a list of all the stocks available (similar to the lookup table in the sample) classified by region.
- camargos88
Anonymous ,
Now you can import the table with the stock name and relate them.
Also if you want, import the stock name and merge it with the initial table. If you need help with that, just paste the table name here.
Ricardo