Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
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"
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.