Forum Discussion

ryan25r9's avatar
ryan25r9
Helper 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:= CALCULATE([Hours], Table[Customer] = "Y")

Billable Hours:= CALCULATE([Hours], Table[Billable] = "Y")

 

& I want to add them together, how do I have the total not duplicate overlapping values where a line is both Customer and Billable?

 

This doesn't appear to work: CALCULATE([Customer Hours] + [Billable 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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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"))
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Assuming Hours is a measure calculated from a column in the 'Table' Table itself, write this measure

    Measure = CALCULATE([Hours],Table[Billable] ="Y",Table[Customer] ="Y")

    Hope this helps.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.

  • VijayP's avatar
    VijayP
    Community Champion
    6 years ago

    =Calculate( sum([Hours]),

                     Filter(All[Tablename], 

                               Table[Customer] = "Y" &&

                               Table[Billable] = "Y"))

     

     

    Try this and let me know if your problem is sorted out.

    If this is the answer for you , please mentioned this is a soltuion and give me Kudos

    I have posted many videos related to this kind of issues  where you can watch www.youtube.com/perepavijay

    Thanks