Forum Discussion

NMC20's avatar
NMC20
Helper I
1 year ago
Solved

Percentage by group

I'm hoping to calculate a % uptake by item but there are different numbers of items available so I need the denominater to change depending on the item name. I would like this split out by time.    ...
Need Help
  • slorin's avatar
    slorin
    1 year ago

    Hi NMC20 

    Another Power Query solution

    let
    Source = Your_Source,
    Unpivot = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Item"}, {{"Data", each _, type table }, {"Sum", each List.Sum([Value]), type number }}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"Attribute", "Value"}, {"Attribute", "Value"}),
    Percentage = Table.CombineColumns(Expand, {"Value", "Sum"}, each _{0}/_{1}, "Percentage"),
    #"Type %" = Table.TransformColumnTypes(Percentage,{{"Percentage", Percentage.Type}}),
    Pivot = Table.Pivot(#"Type %", List.Distinct(#"Type %"[Attribute]), "Attribute", "Percentage", List.Sum)
    in
    Pivot

    Stéphane 

  • MarkLaf's avatar
    MarkLaf
    1 year ago

    What is the original format of your data? From what I'm gleaning, it seems like you have more of a modeling problem than anything else.

     

    For example, based on the table you provided, I would put together a model along the lines of:

     

    Tables

     

    Items

    Item
    10 Door
    4 Door
    6 Door

     

    Availability

    ItemDateAvailabile
    10 Door7/1/202510
    4 Door7/1/202523
    6 Door7/1/202512
    10 Door7/2/202513
    4 Door7/2/202522
    6 Door7/2/202510

     

    Time Slots

    Label
    00:00 - 01:00
    01:00 - 02:00
    02:00 - 03:00
    03:00 - 04:00
    04:00 - 05:00
    05:00 - 06:00
    06:00 - 07:00
    07:00 - 08:00
    08:00 - 09:00
    09:00 - 10:00
    10:00 - 11:00
    11:00 - 12:00
    12:00 - 13:00
    13:00 - 14:00
    14:00 - 15:00
    15:00 - 16:00
    16:00 - 17:00
    17:00 - 18:00
    18:00 - 19:00
    19:00 - 20:00
    20:00 - 21:00
    21:00 - 22:00
    22:00 - 23:00
    23:00 - 24:00

     

    Uptake

    ItemDateTime SlotUptake
    10 Door7/1/202508:00 - 09:001
    10 Door7/2/202508:00 - 09:001
    10 Door7/1/202509:00 - 10:002
    10 Door7/2/202509:00 - 10:002
    10 Door7/1/202510:00 - 11:001
    10 Door7/2/202510:00 - 11:001
    10 Door7/1/202511:00 - 12:003
    10 Door7/2/202511:00 - 12:003
    10 Door7/1/202512:00 - 13:004
    10 Door7/2/202512:00 - 13:004
    4 Door7/1/202508:00 - 09:001
    4 Door7/2/202508:00 - 09:001
    4 Door7/1/202509:00 - 10:001
    4 Door7/2/202509:00 - 10:001
    4 Door7/1/202510:00 - 11:000
    4 Door7/2/202510:00 - 11:000
    4 Door7/1/202511:00 - 12:002
    4 Door7/2/202511:00 - 12:002
    4 Door7/1/202512:00 - 13:003
    4 Door7/2/202512:00 - 13:003
    6 Door7/1/202508:00 - 09:000
    6 Door7/2/202508:00 - 09:000
    6 Door7/1/202509:00 - 10:000
    6 Door7/2/202509:00 - 10:000
    6 Door7/1/202510:00 - 11:002
    6 Door7/2/202510:00 - 11:002
    6 Door7/1/202511:00 - 12:005
    6 Door7/2/202511:00 - 12:005
    6 Door7/1/202512:00 - 13:007
    6 Door7/2/202512:00 - 13:007

     

    Dates

     

    Dates = 
GENERATE(
    CALENDARAUTO(),
    ROW(
        "Year", YEAR( [Date] ),
        "MonthNo", MONTH( [Date] ),
        "Month", FORMAT( [Date], "mmm" )
    )
)

     

     

    Model

     

     

     

    You can then use a visual matrix to get what you are after using the following measure:

     

     

    Uptake / Available % = 
    VAR _uptake = SUM( Uptake[Uptake] )
    VAR _available = SUM( Availability[Available] )
    VAR _openSlots = CALCULATE( COUNTROWS( 'Time Slots' ), 'Time Slots'[Open] )
RETURN
    DIVIDE( _uptake, _available * _openSlots )

     

     

     

SundarRaj's avatar
SundarRaj
Super User
1 year ago

Hi NMC20, here's another solution for your query using M. Thanks

Here's the code:
let
Source = #table(
{"Item", "08:00 - 09:00", "09:00 - 10:00", "10:00 - 11:00", "11:00 - 12:00", "12:00 - 13:00"},
{{"10 Door", 1, 2, 1, 3, 4}, {"4 Door", 1, 0, 2, 3, 4}, {"6 Door", 0, 1, 2, 5, 7}}
),
Cols = List.RemoveLastN(List.Skip(Table.ColumnNames(Source), 1), 1),
Table = Source,
Unpivot = Table.UnpivotOtherColumns(Table, {"Item"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(
Unpivot,
{"Item"},
{{"Data", each _, type table [Item = text, Attribute = text, Value = number]}}
),
TTF = Table.TransformColumns(
#"Grouped Rows",
{
"Data",
each Table.TransformColumns(
Table.DuplicateColumn(_, "Item", "Denom"),
{"Denom", each Total[Total]{List.PositionOf(Total[Item], _)}}
)
}
),
TTS = Table.TransformColumns(
TTF,
{
"Data",
each Table.SelectColumns(
Table.Pivot(
Table.SelectColumns(
Table.AddColumn(_, "Val", each [Value] / [Denom]),
{"Item", "Attribute", "Val"}
),
[Attribute],
"Attribute",
"Val",
List.Sum
),
Cols
)
}
),
Expand = Table.ExpandTableColumn(TTS, "Data", Cols, Cols)
in
Expand