cancel
Showing results for
Did you mean:
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

5 REPLIES 5
Super User III

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?

Thanks,

Pragati

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

Community Support

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:

Best Regards

Allan

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

Resident Rockstar

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"),

in

Impactful Individual

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"),
in
result``````

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

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

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.

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

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

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!