Simple Sum of multiple Columns and re-arrange columns data as rows

[prady2581]

I;ve a table same as shown below example. 

A         B         C      D

User   Email  time  late 

3        50         6       3  

3       6           9        2 

I need to accomplish  following 2 tasks: 

Task 1 : Create a new column as "ABC"  = Sum(A2,B2,D2)  - This works in excel. How to do it in Power BI. ? 

Task 2 : Re-arrange or display in Visualization area above table as rows (see below) :Note : My raw data has these arranged in column wise. 

A         B     C

User   3     3

Email  50   6

Time   9    9

Late   2     2 

[v-yulgu-msft]

Hi @prady2581,

Suppose the sample table imported to Power BI looks like:

Task1

Create a calculated column using this DAX formula:

Sum =
CALCULATE (
    LASTNONBLANK ( 'Sum multiple columns'[User], 1 )
        + LASTNONBLANK ( 'Sum multiple columns'[Email], 1 )
        + LASTNONBLANK ( 'Sum multiple columns'[Time], 1 )
)

Task2

Open Query Editors, you should refer to Power Query to achieve the requirement to re-arrange columns data as rows.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\test.xlsx"), null, true),
    #"Sum multiple columns_Sheet" = Source{[Item="Sum multiple columns",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Sum multiple columns_Sheet"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User", Int64.Type}, {"Email", Int64.Type}, {"Time", Int64.Type}, {"Late", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}}),
    AddRanking = (table, column, newColumn) =>
        Table.AddIndexColumn(Table.Sort(table, {{column, Order.Descending}}), newColumn, 1, 1),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Category"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "Value", "Rank")}}),
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", { "Value", "Rank"}, { "Value", "Rank"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"1", "Value1"}, {"2", "Value2"}})
in
    #"Renamed Columns1"

Result output:

Best regards,
Yuliana Gu