Creating Buckets based on a percentage from measure

[DSwezey]

I have a matrix setup that shows a bunch of different job information (see screenshot below):

One of the columns (which is a measure named [%hours]) calculates the percentage of how many hours actually worked compared to the estimated hours for the job. 

For the sake of this posting I made a small example set:

What I want to do is create buckets for the following protocols:

Jobs 0 hrs to 75% hours,

Jobs > 75% hours and < 90% hours,

Job >= 90% hours and < 100% hours,

Jobs >= 100% hours

I then use these buckets to break the matrix down to look like below (where you can drill down on the % description to show the remaining matrix data):

Any suggestions would be great!

[AlexisOlson]

For your small example set, you can just add a calculated column like

Bucket =
SWITCH (
    TRUE (),
    [% of Hours] < 0.75, "Jobs 0 hrs to 75% hours",
    [% of Hours] < 0.90, "Jobs > 75% hours",
    [% of Hours] < 1.00, "Jobs > 90% hours",
    [% of Hours] >= 1.0, "Jobs >= 100% hours"
)

If your data is more complex, you might need to use more robust static or dynamic segmentation patterns.

https://www.daxpatterns.com/static-segmentation/

https://www.daxpatterns.com/dynamic-segmentation/

[DSwezey]

What I forgot to mention was that [% of hours] is a measure that divides the "Actual Hours" (from Table A) by "Estimated Hours" (from Table B). So I can't necesarily create a calculated column because it references two tables?

[AlexisOlson]

A calculated column can reference other tables. The main limitation with calculated columns is that they cannot be responsive to slicers. So if you expect the buckets to be dynamic, then you need to take a look at dynamic segmentation.

[DSwezey]

What I ended up doing is creating a reference table (named 'Hour Buckets') like below:

I then created a measure to recalculate the [%ofHours] (which is just the actual hours divided by the estimated hours) based on the reference table buckets.

HourlyBuckets = 
CALCULATE(
    [%ofHours],
    FILTER(
        VALUES('Job Number'[Job Number]),
            COUNTROWS(
                FILTER(
                    'Hour Buckets',
                    [%ofHours] >= 'Hour Buckets'[Min] &&
                    [%ofHours] < 'Hour Buckets'[Max]
                )
            ) > 0
    )
)

The problem with this is there's no relationship for this reference table to Table A and Table B in [%ofHours].

This visual is before applying the "Categories" row filter. As you can see, the "HourlyBuckets" column appropriately calculates the percentage and also displays it.

This visual is after applying the "Categories" filter at the top row level

But when you apply the categories as a row filter it no longer calculates every jobs hourly percentage.

Not only this, but each bucket now contains every job regardless if it should be in that bucket or not. 

Not sure how to make a better relationship for this setup.