Forum Discussion
NMC20
1 year agoHelper I
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. ...
- 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
PivotStéphane
- 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
Item Date Availabile 10 Door 7/1/2025 10 4 Door 7/1/2025 23 6 Door 7/1/2025 12 10 Door 7/2/2025 13 4 Door 7/2/2025 22 6 Door 7/2/2025 10 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
Item Date Time Slot Uptake 10 Door 7/1/2025 08:00 - 09:00 1 10 Door 7/2/2025 08:00 - 09:00 1 10 Door 7/1/2025 09:00 - 10:00 2 10 Door 7/2/2025 09:00 - 10:00 2 10 Door 7/1/2025 10:00 - 11:00 1 10 Door 7/2/2025 10:00 - 11:00 1 10 Door 7/1/2025 11:00 - 12:00 3 10 Door 7/2/2025 11:00 - 12:00 3 10 Door 7/1/2025 12:00 - 13:00 4 10 Door 7/2/2025 12:00 - 13:00 4 4 Door 7/1/2025 08:00 - 09:00 1 4 Door 7/2/2025 08:00 - 09:00 1 4 Door 7/1/2025 09:00 - 10:00 1 4 Door 7/2/2025 09:00 - 10:00 1 4 Door 7/1/2025 10:00 - 11:00 0 4 Door 7/2/2025 10:00 - 11:00 0 4 Door 7/1/2025 11:00 - 12:00 2 4 Door 7/2/2025 11:00 - 12:00 2 4 Door 7/1/2025 12:00 - 13:00 3 4 Door 7/2/2025 12:00 - 13:00 3 6 Door 7/1/2025 08:00 - 09:00 0 6 Door 7/2/2025 08:00 - 09:00 0 6 Door 7/1/2025 09:00 - 10:00 0 6 Door 7/2/2025 09:00 - 10:00 0 6 Door 7/1/2025 10:00 - 11:00 2 6 Door 7/2/2025 10:00 - 11:00 2 6 Door 7/1/2025 11:00 - 12:00 5 6 Door 7/2/2025 11:00 - 12:00 5 6 Door 7/1/2025 12:00 - 13:00 7 6 Door 7/2/2025 12:00 - 13:00 7 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 )
jgeddes
1 year agoSuper User
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"