Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating Buckets based on a percentage from measure

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.