DAX Help - Compute Correct Total

[Dan_K_Howe99]

Total = Rate * Amount

I need a DAX expression that will return the correct total for both CA & FL (5,000 + 2,500 = 7,500)

Current expression is returning 15,000

Below returns 15,000

VAR _RATE = SUMX( VALUES('table'[State]), SUM('Table'[Rate]))
VAR _TOTAL = [Amount] * _RATE

Essentially, need an expression that will sum the Total column in first table.

Thank you

[Greg_Deckler]

@Dan_K_Howe99 In addition to @adudani see if this information is helpful as well:

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[adudani · Accepted solution]

Hi @Dan_K_Howe99 

Create a measure with the following DAX Code:

Total = sumx( 'Table', 'Table'[Rate]*'Table'[Amount])

_____________________________________________
Alternatively, 

1. Create a custom column"Total" in Power Query Editor = [Rate]*[Amount]

2. In the report view, create a measure Total = Sum([Total])

Power Query code for calculated column: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnZU0lEy0DMFkoYGQKAUqxOtFOwGFzQFi8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Rate = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Rate", type number}, {"Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Total", each [Rate]*[Amount]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Total", type number}})
in
#"Changed Type1"


Please accept this as the solution if it resolves your query.

Appreciate a thumbs up if it helps.