cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

mem.PNG

5 REPLIES 5
Highlighted
Super User IV
Super User IV

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Solution Supplier
Solution Supplier

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

Highlighted
Community Support
Community Support

Hi, @Anonymous 

 

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

 

Table:

f1.png

 

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:

f2.png

 

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.

 

Highlighted
Super User II
Super User II

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





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted

 

 

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"

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors