Customize table formation

[Vish_korg]

I am completey new to Power Bi .I require help to make a new table for the following data.

Existing table
Losses	Down Time
Break down	30
Start up loss	20
Clita	10
Break down	10
Set up loss	15
clita	5
Cleaning	10
Set up loss	10

Require the following output             

Required out put
Losses	Down Time sum	Down time average	% of total time
Break down	40	20	36%
Start up loss	20	20	18%
Clita	15	7.5	14%
Cleaning	10	10	9%
Set up loss	25	12.5	23%
	110	14	100%

Can any one help?

[BA_Pete · Accepted solution]

Hi @Vish_korg ,

You would do this using measures in your model and a basic table visual.

Your measures would be calculated as follows, where 'YourTable' should be replaced with the actual name of your table in the model:

Down Time Sum = SUM(YourTable[Down Time])

Down Time Avg = AVERAGE(YourTable[Down Time])

% Total Down Time = 
VAR __TotalTime =
CALCULATE(
    SUM(YourTable[Down Time]),
    ALLSELECTED(YourTable[Losses])
)
RETURN
DIVIDE([Down Time Sum], __TotalTime, 0)

Using these measures in a table visual with the [Losses] column from YourTable gives the following output:

Pete

[shafiz_p · Accepted solution]

Hi @Vish_korg , Try belwo M code in power query advance option:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Losses", type text}, {"Down Time", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Losses"}, {{"Down Time", each List.Sum([Down Time]), type nullable number}, {"Down Time Avg", each List.Average([Down Time]), type nullable number}}),
    TotalSum = List.Sum(#"Grouped Rows"[Down Time]),
    AddPercentTotal = Table.AddColumn(#"Grouped Rows", "% of Total Time", each [Down Time]/TotalSum),
    #"Changed Type1" = Table.TransformColumnTypes(AddPercentTotal,{{"% of Total Time", Percentage.Type}})
in
    #"Changed Type1"

See images below for different steps:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[p45cal]

Note that the source table had case differences (Clita and clita), so the grouping needs to be case insensitive:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupedRows = Table.Group(Source, {"Losses"}, {{"DT Sum", each List.Sum([Down Time]), type number}, {"DT average", each List.Average([Down Time]), type number},{"% Total Time",each List.Sum([Down Time])/List.Sum(Source[Down Time]),Percentage.Type}},null,Comparer.OrdinalIgnoreCase)
in
    GroupedRows

Note also a difference in the average 'total' at the bottom, the OP has 14 whereas @BA_Pete's has 13.8 (real value 13.75) because the one is the average of the grouped averages, the other the average of the original ungrouped values.

[BA_Pete · Accepted solution]

Hi @Vish_korg ,

You would do this using measures in your model and a basic table visual.

Your measures would be calculated as follows, where 'YourTable' should be replaced with the actual name of your table in the model:

Down Time Sum = SUM(YourTable[Down Time])

Down Time Avg = AVERAGE(YourTable[Down Time])

% Total Down Time = 
VAR __TotalTime =
CALCULATE(
    SUM(YourTable[Down Time]),
    ALLSELECTED(YourTable[Losses])
)
RETURN
DIVIDE([Down Time Sum], __TotalTime, 0)

Using these measures in a table visual with the [Losses] column from YourTable gives the following output:

Pete

[Vish_korg]

Works perfectly.

Thanks