Exclude overlapping results when adding measures together

[ryan25r9]

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])

[v-alq-msft · Accepted solution]

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.

[Ashish_Mathur]

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.

[amitchandak]

@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"))

[VijayP]

=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