Percentage by group

[NMC20]

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. 

I've managed to do the split out by time but only as a whole number. I would like this to be a % of available items. 

Example:

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	1	0	2	3
6 Door	0	0	2	5	7

Available:

10 Door - 10

4 Door - 23

6 Door - 12

So the % I would expect is below, I just don't know how to calculate it in Power BI

Item	08:00 - 09:00	09:00 - 10:00	10:00 - 11:00	11:00 - 12:00	12:00 - 13:00
10 Door	10%	20%	10%	30%	40%
4 Door	4.3%	4.3%	0%	8.7%	13%
6 Door	0%	0%	16.7%	41.7%	58.3%

Thanks in advance!

[SundarRaj]

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

[jgeddes]

Here is another way you could do this in M.
Adding a column with the row sum

and then select the Item and RowSum column and "unpivoting other columns"

you can then divide the count by the row sum.

You can use the resulting table to create a matrix visual similar to your desired output.

Here is the example M code.

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMjRQcMnPL1LSUTIEYiMobQzEJkqxOtFKJsjSIGwAVWYMljaDSRsgSZkCsblSbCwA", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"08:00 - 09:00" = _t, #"09:00 - 10:00" = _t, #"10:00 - 11:00" = _t, #"11:00 - 12:00" = _t, #"12:00 - 13:00" = _t]
    ),
    #"Added Custom" = 
    Table.AddColumn(
        Source, 
        "RowSum", 
        each 
        List.Sum(
            List.Transform(
                Record.ToList(
                    Record.RemoveFields(_, {"Item"})
                ), 
                each Number.FromText(_)
            )
        ), 
        Int64.Type
    ),
    #"Unpivoted Other Columns" = 
    Table.UnpivotOtherColumns(
        #"Added Custom", 
        {"Item", "RowSum"}, 
        "Time", 
        "Value"
    ),
    #"Add Percentage" = 
    Table.AddColumn(
        #"Unpivoted Other Columns", 
        "Percentage", 
        each Number.FromText([Value]) / [RowSum], 
        type number
    ),
    #"Remove RowSum" = 
    Table.RemoveColumns(
        #"Add Percentage",
        {"RowSum"}
    )
in
    #"Remove RowSum"

[Demert]

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]

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]

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