Forum Discussion
Percentage by group
- 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 )
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 )