SUMPRODUCT - Power BI

[Anonymous]

Implemented a SUMPRODUCT forumla in excel to strip the data of a row. Trying to do the same in Power BI, although no previous details fit my case. Using 

=SUMPRODUCT(2^{0,1,2,3,4,5,6,7,8,9},(1+LEN(A3)-LEN(SUBSTITUTE("|"&A3,"|"&2^{0,1,2,3,4,5,6,7,8,9}&"GB","")))/{4,4,4,4,5,5,5,6,6,6})&"GB" - forumla in excel to do (See attached pic below) - THANKS !! need help guys

[Pragati11]

Hi @Anonymous ,

Can you explain in detail what you are trying to achieve in the SUMPRODUCT formula in excel, so that a Power BI solution can be provided?

Also, refer following link on how to get your question answered:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure like below.

Result = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Memory],
        "Result",
        var _memory = [Memory]
        return
        SUMX(
            FILTER(
                ALL('Table'),
                'Table'[Memory] = _memory
            ),
            VALUE(SUBSTITUTE( [Official Memory],"GB",""))
        )
    ),
    [Result]
)

Result:

If I misunderstand your thoughts, please show us some sample data and expected result with OneDrive for business. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Just a variant of  @ziying35  solution, to be more adherent to the excel formula, which does not simply add to the sum of the numbers preceding "GB" but only add them if they are powers of 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsnB3UjAyNDZWyE3PqFKK1aGRSA0heROS9RsboSgwNCNggqExyVaYkOQFNPlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

 ram= (str)=> Number.ToText(List.Sum(List.Intersect({List.Transform(Text.Split(str,"|"),each Number.From(Text.BeforeDelimiter(_,"GB"))),{1,2,4,8,16,32,128,256,512,1024}})),"0GB"),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each ram([Column1]))
in
    #"Added Custom"

[ziying35]

Hi, @Anonymous 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8k3NzS+qVLJSsnB3UjAyNDZW8PWoUqrVwSFTg1uZCbIyExRlsQA=",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (str)=> Number.ToText(List.Sum(List.Transform(Text.Split(str,"|"),each Number.From(Text.BeforeDelimiter(_,"GB")))),"0GB"),
    result = Table.AddColumn(Source,"t",each fx([Memory]))
in
    result

If my code solves your problem, mark it as a solution

[Greg_Deckler]

@Anonymous - SUMPRODUCT DAX equivalent is found here:

https://community.powerbi.com/t5/Community-Blog/S-Excel-to-DAX-Translation/ba-p/1061121

Hard to tell what is going on in your formula. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.