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 )
Hi NMC20,
Thank you for reaching out to the Microsoft Fabric Forum Community. And also thanks to Demert , SundarRaj and jgeddes for prompt and helpful responses.
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
Thank you so much for your help!
Unfortunately, the SUMMARIZE option did not work in the way I expected. I am attempting the M option but I've not used that before so having to learn a little bit and struggling for time to do so currently!
I do have a question on that. When I open Advanced Editor, I already have a "LET" "IN" query in there. How do I insert the below step? Can I have 2 x LET/INs in 1 or do I need to add a step?