Ensuring that my measure also applies the filter

[BruceSV]

Hi everyone, 

I am trying to calculate the utilization of a team of people per period. But I am stuck with an error. 

I have two tables. One fact table that holds the time_spent value and a dimensions table that holds the total_capacity of the team member value.

The calculation is fairly straigth forward: 

(time_spent / total_capacity) * 100%

So my dax measure is as follows:

Utilization Percentage = CALCULATE(
    DIVIDE(
        SUM(tbl_1[time_spent]),
        SUM(tbl_2[total_capacity])), 
        ALLSELECTED('tbl_1')
    *100)

However this is giving me an "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

If I remove the "ALLSELECTED" clause the code works but the measure does not take some filters into consideration. Giving me incorrect information.

What I essentially want is a measure that calculates the utilization % but still respects all the applied filters on the visual.

[ERD · Accepted solution]

@BruceSV , I think you've put *100 in the wrong place.. 

Utilization Percentage =
CALCULATE (
    DIVIDE ( SUM ( tbl_1[time_spent] ), SUM ( tbl_2[total_capacity] ) ) * 100,
    ALLSELECTED ( 'tbl_1' )
)

[BruceSV]

Although the syntax is approved the results are not what I expected. The measure now doesnt apply the legend anymore. 

Edit - It actually did work and surfaces a different problem, need to update my data model. Thanks @ERD 

[some_bih]

Hi @BruceSV ALLSELECTED is most compex function in DAX.

Usually you should have  some aggregation in case of this error.

My question to you why in your ALLSELECTED you put table_1 as in CALCULCATE there are also table_2 and why it is *100) at end? Did you try pure ALLSELECTED ()?

[BruceSV]

Hi @some_bih - I have tried this but that doesn't work unfortunately.