Forum Discussion

Vish_korg's avatar
Vish_korg
Frequent Visitor
1 year ago
Solved

Customize table formation

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?

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    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's avatar
    shafiz_p
    1 year ago

    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

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

    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's avatar
    shafiz_p
    Super User
    1 year ago

    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's avatar
      p45cal
      Solution Supplier
      1 year ago

      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.