Conversion of Excel formula to DAX

[Anonymous]

Hi,

I am struggling with a formula I need to convert from Excel to DAX. I have attached an image below of the sample data set, as well as the existing 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=B$19))/100

Basically, I have one table in PowerBI with a number of investor portfolios and their allocations towards a limited set of stocks, and another table with the total set of available stocks (with their region classification). 

What I need to do is to summarize each investor's allocation towards each region. All I need is the allocation in %, the amount in USD is irrelevant.

The problem for me, so far, seems to be that the "TEXT" function does not exist in DAX, and I have not been able to find a solution with alternative formulas. Please note, solving the problem using PowerQuery unfortunately is not an option in this case (for the purpose of the assignment, the solution needs to be purely in DAX, preferably as a measure rather than calculated column).

Thank you very much in advance. Will be happy to provide a sample workbook if preferred.

[camargos88]

Hi @Anonymous ,

Check this link: https://community.powerbi.com/t5/Desktop/Single-cell-stock-allocations/m-p/1054559#M493371

[Anonymous]

Thanks Camargos,

Unfortunately, that solution relies on Power Query to do the calculations rather than a PowerPivot / DAX formula.

In this particular case that is unfortunately not an option.

Best,

Phil