Sumproduct Dax formula returning wrong values

[dokat]

Hi,

I have below 'Summary' table where i would like to multipy Dollar LY with Price Chg column. Basically same calculation as sumproduct formula in Excel.  I tried below Dax formula however all it does is multuiplying Dollar LY values with -1 and returning wrong values. Can anyone help with correct dax formula to calculate sumproducts for column Dollar LY and Price Chg (sumproduct(Dollar LY,Price Chg). Appreciate any help!

Weighted Price = SUMX('Summary', 'Summary'[Dollar LY] *'Summary' [Price Chg])

[v-henryk-mstf · Accepted solution]

Hi @dokat ,

You can first convert the table in the power query. Then create the following formula.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdA9C8MgEIDhvyKCW4b4cV5c29Kp0LlIBinSFtIWzP+HGjueuU3h4TzfGKWWgzzl9BHnVN7rdvkuSyricqtnDT6gnIcoDcuMC9CY5ZjTevS6Occ5hAldY8AxY8YwNebZ5azHP0OW1U+Ab44kOaQ1i2t53bM4Ph/b06ofhUCr+ln2JpIuexNJGQK16rchEFS/Dt0Rq5x/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Manufacture = _t, Measure = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Manufacture", type text}, {"Measure", type text}, {"Values", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type",(x)=>x[Product]=_[Product])),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Product"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom.1", each Table.SelectRows([Custom],(n)=>n[Measure]<>_[Measure])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Product", "Manufacture", "Measure", "Values"}, {"Custom.1.Product", "Custom.1.Manufacture", "Custom.1.Measure", "Custom.1.Values"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom.1.Product", "Custom.1.Manufacture", "Custom.1.Measure"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1.Values", "Percentage"}})
in
    #"Renamed Columns"

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[StefanoGrimaldi]

but thats not what sumx does, try the formula Isend and replicate a table with the same strcuture visual as your example, for what you saing use the one for new column one that will create exactly what you lookinf for and be able to dax upon the new measure more easily

[dokat]

@StefanoGrimaldi Thanks for your reply.

Below you will find the sample file. I am trying to calculate sumproduct wtd price using "Base Price Impact" metric in the file. Please let me know if you need anything else. Thanks

Here is the formula i am using. 

Base Price Impact = DIVIDE(SUMX('Summary',[Dollar LY] * [Base Price Chg]),[Dollar LY])

Here is what i am trying to achieve.

https://www.dropbox.com/s/50chxwi62gyirew/Test.pbix?dl=0

[StefanoGrimaldi]

Weighted Price = (SUM('Summary'[Dollar LY]) ) * (SUM('Summary' [Price Chg])) if its for a measure, 

for a column just Weighted Price = 'Summary'[Dollar LY] *'Summary' [Price Chg]

[dokat]

@StefanoGrimaldi Thank you for your response. I am trying to calculate weight average that's why need to use sumproduct formula. however sumx formula not returning correct value i think because of the data structure. All my metrics are under measures column and all values are under values column. Please see below example.

Measure	Values
Dollar TY
Dollar TY
Dollar TY	$411,061
Dollar TY	$75,874