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,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar