Forum Discussion

NMC20's avatar
NMC20
Helper I
1 year ago
Solved

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.    ...
Need Help
  • slorin's avatar
    slorin
    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
    Pivot

    Stéphane 

  • MarkLaf's avatar
    MarkLaf
    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

    ItemDateAvailabile
    10 Door7/1/202510
    4 Door7/1/202523
    6 Door7/1/202512
    10 Door7/2/202513
    4 Door7/2/202522
    6 Door7/2/202510

     

    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

    ItemDateTime SlotUptake
    10 Door7/1/202508:00 - 09:001
    10 Door7/2/202508:00 - 09:001
    10 Door7/1/202509:00 - 10:002
    10 Door7/2/202509:00 - 10:002
    10 Door7/1/202510:00 - 11:001
    10 Door7/2/202510:00 - 11:001
    10 Door7/1/202511:00 - 12:003
    10 Door7/2/202511:00 - 12:003
    10 Door7/1/202512:00 - 13:004
    10 Door7/2/202512:00 - 13:004
    4 Door7/1/202508:00 - 09:001
    4 Door7/2/202508:00 - 09:001
    4 Door7/1/202509:00 - 10:001
    4 Door7/2/202509:00 - 10:001
    4 Door7/1/202510:00 - 11:000
    4 Door7/2/202510:00 - 11:000
    4 Door7/1/202511:00 - 12:002
    4 Door7/2/202511:00 - 12:002
    4 Door7/1/202512:00 - 13:003
    4 Door7/2/202512:00 - 13:003
    6 Door7/1/202508:00 - 09:000
    6 Door7/2/202508:00 - 09:000
    6 Door7/1/202509:00 - 10:000
    6 Door7/2/202509:00 - 10:000
    6 Door7/1/202510:00 - 11:002
    6 Door7/2/202510:00 - 11:002
    6 Door7/1/202511:00 - 12:005
    6 Door7/2/202511:00 - 12:005
    6 Door7/1/202512:00 - 13:007
    6 Door7/2/202512:00 - 13:007

     

    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's avatar
Poojara_D12
Super User
1 year ago

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.