Forum Discussion

ryan25r9's avatar
ryan25r9
Icon for Helper I rankHelper I
6 years ago
Solved

Exclude overlapping results when adding measures together

I feel like I'm missing something simple, but is there a way to exclude overlapping values when you add measures together?   For example, if I have two separately defined measures: Customer Hours:...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, ryan25r9 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

     

    Result = 
CALCULATE(
    SUM('Table'[Hours]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer Hours] = "Y"
    )
)+
CALCULATE(
    SUM('Table'[Hours]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Billable Hours] = "Y"
    )
)-
CALCULATE(
    SUM('Table'[Hours]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer Hours] = "Y"&&
        'Table'[Billable Hours] = "Y"
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

ryan25r9, Try OR and AND depend on need

Customer Hours:= CALCULATE([Hours], filter(Table,Table[Customer] = "Y" || Table[Billable] = "Y"))
Customer Hours:= CALCULATE([Hours], filter(Table,Table[Customer] = "Y" && Table[Billable] = "Y"))