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 )
Anonymous
1 year agoNot applicable
Hi NMC20,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
Warm regards,
Prasanna Kumar