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 )

     

     

     

Demert's avatar
Demert
Resolver III
1 year ago

Hi NMC20 ,

 

This is a difficult problem. What i suggest is creating in PowerQuery a new conditional column with the following statements: 

 

Next up creating a new summarized table of the current table you have:

_NewTable = SUMMARIZE('Table','Table'[Item],
"08:00 - 09:00", DIVIDE(MAX('Table'[08:00 - 09:00]),MAX('Table'[Denominator])),
"09:00 - 10:00", DIVIDE(MAX('Table'[09:00 - 10:00]),MAX('Table'[Denominator])),
"10:00 - 11:00", DIVIDE(MAX('Table'[10:00 - 11:00]),MAX('Table'[Denominator])),
"11:00 - 12:00", DIVIDE(MAX('Table'[11:00 - 12:00]),MAX('Table'[Denominator])),
"12:00 - 13:00", DIVIDE(MAX('Table'[12:00 - 13:00]),MAX('Table'[Denominator]))
)

 

which has the following output

 

NMC20's avatar
NMC20
Helper I
1 year ago

Thank you for this! I'm getting the error message "Argument '33' in SUMMARIZE function is required" when I recreate this at the summary table stage. My syntax appears to be the same as yours so I'm not sure what I've done wrong. 

_NewTable = SUMMARIZE(  'All Payment Data', 'All Payment Data'[Locker Size],
"08:00 - 09:00", DIVIDE(MAX('All Payment Data'[08:00 - 09:00]),MAX('All Payment Data'[Denominator])),
"09:00 - 10:00", DIVIDE(MAX('All Payment Data'[09:00 - 10:00]),MAX('All Payment Data'[Denominator])),
"10:00 - 11:00", DIVIDE(MAX('All Payment Data'[10:00 - 11:00]),MAX('All Payment Data'[Denominator])),
"11:00 - 12:00", DIVIDE(MAX('All Payment Data'[11:00 - 12:00]),MAX('All Payment Data'[Denominator])),
"12:00 - 13:00", DIVIDE(MAX('All Payment Data'[12:00 - 13:00]),MAX('All Payment Data'[Denominator])),
"13:00 - 14:00", DIVIDE(MAX('All Payment Data'[13:00 - 14:00]),MAX('All Payment Data'[Denominator])),
"14:00 - 15:00", DIVIDE(MAX('All Payment Data'[14:00 - 15:00]),MAX('All Payment Data'[Denominator])),
"15:00 - 16:00", DIVIDE(MAX('All Payment Data'[15:00 - 16:00]),MAX('All Payment Data'[Denominator])),
"16:00 - 17:00", DIVIDE(MAX('All Payment Data'[16:00 - 17:00]),MAX('All Payment Data'[Denominator])),
"17:00 - 18:00", DIVIDE(MAX('All Payment Data'[17:00 - 18:00]),MAX('All Payment Data'[Denominator])),
"18:00 - 19:00", DIVIDE(MAX('All Payment Data'[18:00 - 19:00]),MAX('All Payment Data'[Denominator])),
"19:00 - 20:00", DIVIDE(MAX('All Payment Data'[19:00 - 20:00]),MAX('All Payment Data'[Denominator])),
"20:00 - 21:00", DIVIDE(MAX('All Payment Data'[20:00 - 21:00]),MAX('All Payment Data'[Denominator])),
"21:00 - 22:00", DIVIDE(MAX('All Payment Data'[21:00 - 22:00]),MAX('All Payment Data'[Denominator])),
"22:00 - 23:00", DIVIDE(MAX('All Payment Data'[22:00 - 23:00]),MAX('All Payment Data'[Denominator])),
)
  • Demert's avatar
    Demert
    Resolver III
    1 year ago

    Hi,

     

    This is because at the last line ""22:00 - 23:00", DIVIDE(MAX('All Payment Data'[22:00 - 23:00]),MAX('All Payment Data'[Denominator]))," you still have a comma which shouldn't be there because it's the end of your summarize