Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

SUMPRODUCT - Power BI

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

Need Help

5 Replies

  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago

    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's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

       

       

      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"