Filter measure results when another field added

[vxmine]

Hello,

I have a series of measures to calculate utilization. Here are the measures:

Hours Worked = SUMX(ADP, ADP[Regular Hours]+ADP[Overtime Hours])

Labor Hours = (SUMX(LABOR_TICKET, LABOR_TICKET[HOURS_WORKED]))

Utilization = DIVIDE(SUMX(LABOR_TICKET,LABOR_TICKET[HOURS_WORKED]),SUMX(ADP,ADP[Total Hours]))

By itself, this works well. However, when I pull in a field from another table, the calculations include departments that do not have labor hours my cards and chart averages change. I tried to build a filtered dax measure, but I'm not clear on how to filter when paired with Description.  I can make this work through a normal filter, but this doesn't work when the visual is a card or bar chart. Thanks in advance!

[v-gizhi-msft · Accepted solution]

Hi,

Please try to use ISINSCOPE function to show the correct average in total, like this:

Utilization =
IF (
    ISINSCOPE ( 'Main table'[Description] ),
    DIVIDE ( [Labor Hours], [Hours Worked], BLANK () ),
    AVERAGEX (
        DISTINCT ( 'Main table'[Description] ),
        CALCULATE ( DIVIDE ( [Labor Hours], [Hours Worked], BLANK () ) )
    )
)

See my attached test pbix file.

Best Regards,

Giotto

[vxmine]

Hi gizhi,

That seems to have worked. Thank you!

[parry2k]

@vxmine if you can share sample data with the expected output, it will help to provide the solution. If multiple tables involved, share their relationship.