Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dax Calculation to Add Multiple Columns Together

Hi! Here is a sample of my dataset below. Would someone be able to help me write a DAX calculation to add up the total retirements for all of the months per business unit. The file will be updated monthly so right now I only have January and February, but would like to make sure that as the remaining months of the year added to the spreadsheet that the data model is automatically calculating the added columns. Thanks!

 

Business UnitJan RetFeb Ret 
A11 
B22 
Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    Power BI doesn't like this format which probably came from Excel.  Unpivot it to make it usable, and then the totals will be much easier.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE41idaCUnIMsIjGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Business Unit" = _t, #"Jan Ret" = _t, #"Feb Ret" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Business Unit"}, "Month", "Value")
in
    #"Unpivoted Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".