Percentage by group

[NMC20]

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. 

I've managed to do the split out by time but only as a whole number. I would like this to be a % of available items. 

Example:

Item	08:00 - 09:00	09:00 - 10:00	10:00 - 11:00	11:00 - 12:00	12:00 - 13:00
10 Door	1	2	1	3	4
4 Door	1	1	0	2	3
6 Door	0	0	2	5	7

Available:

10 Door - 10

4 Door - 23

6 Door - 12

So the % I would expect is below, I just don't know how to calculate it in Power BI

Item	08:00 - 09:00	09:00 - 10:00	10:00 - 11:00	11:00 - 12:00	12:00 - 13:00
10 Door	10%	20%	10%	30%	40%
4 Door	4.3%	4.3%	0%	8.7%	13%
6 Door	0%	0%	16.7%	41.7%	58.3%

Thanks in advance!

[slorin · Accepted solution]

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
Pivot

Stéphane 

[v-pgoloju]

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

[v-pgoloju]

Hi @NMC20,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @MarkLaf, @Poojara_D12 , @slorin  and @Demert  for their prompt and helpful responses.

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[MarkLaf · Accepted solution]

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 )

[Poojara_D12]

Hi @NMC20 

o calculate the % uptake by item over time in Power BI—where each item has a different number of available units—you need to dynamically adjust the denominator for each item based on its availability. The goal is to convert raw counts (e.g., doors used per hour) into a percentage of total available units for that item, broken down by hourly time slots. You've already achieved the time-based breakdown, which is great, but converting to percentages requires bringing in the availability data and using it in a DAX measure. The best approach is to create a separate table with two columns: one for Item and one for Available Units. Then, create a relationship between this table and your main usage data via the Item column. Once the relationship is in place, create a measure using DAX like this:

% Uptake = 
DIVIDE(
    SUM('UsageData'[Used]),
    RELATED('Availability'[Available Units])
)

This measure calculates the usage for each item and hour and divides it by that item's total available units. Power BI will then respect the item-level granularity during visualization, showing the correct percentage per hour per item. You can format this measure as a percentage, and use it in your matrix visual with Item on rows and time slots as columns. This setup ensures that each item uses its own denominator, and the result reflects true percentage uptake as you'd expect.

[v-pgoloju]

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

[slorin · Accepted solution]

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
Pivot

Stéphane 

[v-pgoloju]

Hi @NMC20,

No, you cannot have two let and in blocks in the same query each query can only have one let and one in. Once you open the Advanced Editor, you can clear the existing code and paste the code provided in the relevant response. Then, update the connection string in the Source step to match your setup, and click OK to apply the changes.

if the issue still persists Please reach out to us, to assist further.

Thanks & regards,

Prasanna Kumar

[v-pgoloju]

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

[NMC20]

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? 

[SundarRaj]

Hi @NMC20, here's another solution for your query using M. Thanks

Here's the code:
let
Source = #table(
{"Item", "08:00 - 09:00", "09:00 - 10:00", "10:00 - 11:00", "11:00 - 12:00", "12:00 - 13:00"},
{{"10 Door", 1, 2, 1, 3, 4}, {"4 Door", 1, 0, 2, 3, 4}, {"6 Door", 0, 1, 2, 5, 7}}
),
Cols = List.RemoveLastN(List.Skip(Table.ColumnNames(Source), 1), 1),
Table = Source,
Unpivot = Table.UnpivotOtherColumns(Table, {"Item"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(
Unpivot,
{"Item"},
{{"Data", each _, type table [Item = text, Attribute = text, Value = number]}}
),
TTF = Table.TransformColumns(
#"Grouped Rows",
{
"Data",
each Table.TransformColumns(
Table.DuplicateColumn(_, "Item", "Denom"),
{"Denom", each Total[Total]{List.PositionOf(Total[Item], _)}}
)
}
),
TTS = Table.TransformColumns(
TTF,
{
"Data",
each Table.SelectColumns(
Table.Pivot(
Table.SelectColumns(
Table.AddColumn(_, "Val", each [Value] / [Denom]),
{"Item", "Attribute", "Val"}
),
[Attribute],
"Attribute",
"Val",
List.Sum
),
Cols
)
}
),
Expand = Table.ExpandTableColumn(TTS, "Data", Cols, Cols)
in
Expand

[jgeddes]

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"

[Demert]

Hi @NMC20 ,

This is a difficult problem. What i suggest is creating in PowerQuery a new conditional column with the following statements: 

Next up creating a new summarized table of the current table you have:

_NewTable = SUMMARIZE('Table','Table'[Item],
"08:00 - 09:00", DIVIDE(MAX('Table'[08:00 - 09:00]),MAX('Table'[Denominator])),
"09:00 - 10:00", DIVIDE(MAX('Table'[09:00 - 10:00]),MAX('Table'[Denominator])),
"10:00 - 11:00", DIVIDE(MAX('Table'[10:00 - 11:00]),MAX('Table'[Denominator])),
"11:00 - 12:00", DIVIDE(MAX('Table'[11:00 - 12:00]),MAX('Table'[Denominator])),
"12:00 - 13:00", DIVIDE(MAX('Table'[12:00 - 13:00]),MAX('Table'[Denominator]))
)

which has the following output

[NMC20]

Thank you for this! I'm getting the error message "Argument '33' in SUMMARIZE function is required" when I recreate this at the summary table stage. My syntax appears to be the same as yours so I'm not sure what I've done wrong. 

_NewTable = SUMMARIZE(  'All Payment Data', 'All Payment Data'[Locker Size],

"08:00 - 09:00", DIVIDE(MAX('All Payment Data'[08:00 - 09:00]),MAX('All Payment Data'[Denominator])),

"09:00 - 10:00", DIVIDE(MAX('All Payment Data'[09:00 - 10:00]),MAX('All Payment Data'[Denominator])),

"10:00 - 11:00", DIVIDE(MAX('All Payment Data'[10:00 - 11:00]),MAX('All Payment Data'[Denominator])),

"11:00 - 12:00", DIVIDE(MAX('All Payment Data'[11:00 - 12:00]),MAX('All Payment Data'[Denominator])),

"12:00 - 13:00", DIVIDE(MAX('All Payment Data'[12:00 - 13:00]),MAX('All Payment Data'[Denominator])),

"13:00 - 14:00", DIVIDE(MAX('All Payment Data'[13:00 - 14:00]),MAX('All Payment Data'[Denominator])),

"14:00 - 15:00", DIVIDE(MAX('All Payment Data'[14:00 - 15:00]),MAX('All Payment Data'[Denominator])),

"15:00 - 16:00", DIVIDE(MAX('All Payment Data'[15:00 - 16:00]),MAX('All Payment Data'[Denominator])),

"16:00 - 17:00", DIVIDE(MAX('All Payment Data'[16:00 - 17:00]),MAX('All Payment Data'[Denominator])),

"17:00 - 18:00", DIVIDE(MAX('All Payment Data'[17:00 - 18:00]),MAX('All Payment Data'[Denominator])),

"18:00 - 19:00", DIVIDE(MAX('All Payment Data'[18:00 - 19:00]),MAX('All Payment Data'[Denominator])),

"19:00 - 20:00", DIVIDE(MAX('All Payment Data'[19:00 - 20:00]),MAX('All Payment Data'[Denominator])),

"20:00 - 21:00", DIVIDE(MAX('All Payment Data'[20:00 - 21:00]),MAX('All Payment Data'[Denominator])),

"21:00 - 22:00", DIVIDE(MAX('All Payment Data'[21:00 - 22:00]),MAX('All Payment Data'[Denominator])),

"22:00 - 23:00", DIVIDE(MAX('All Payment Data'[22:00 - 23:00]),MAX('All Payment Data'[Denominator])),

)

[Demert]

Hi,

This is because at the last line ""22:00 - 23:00", DIVIDE(MAX('All Payment Data'[22:00 - 23:00]),MAX('All Payment Data'[Denominator]))," you still have a comma which shouldn't be there because it's the end of your summarize