Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Help - Compute Correct Total

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

  • adudani's avatar
    adudani
    3 years ago

    Hi Anonymous 

    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.

2 Replies