SUMX with filters from different tables

[ejoneslor]

I am trying to calculate and average value for some sales data which works in conjunction with my slicers.

I have some DAX code below which works really well when returning for all of our quoted work.
I need to add an extra filter in to do the same calulation but only when the quote has been accepted. I've tried a number of ways but the values returned are not correct.

The acceptance sits in another table which is connected to my already filtered data via another table. The structure is...

Opportunities, One to Many with Quotes

Quotes, One to Many with Quote Line Items

The filter I need to add is 

'Opportunities', Opportunities[StageName] = "Closed Won"

The working DAX I have is 

AveWeeklyRateWon =

SUMX (

    DISTINCT ( 'Date'[Date]),

    CALCULATE (

        SUMX(

            FILTER (

               'Quote Line Items',

                'Quote Line Items'[CreatedDateOnly] = MAX ('Date'[Date])

                    &&'Quote Line Items'[Header_Product__c] = TRUE

                    &&'Quote Line Items'[Selling_Type__c] = "Hire"

            ),

            'Quote Line Items'[Sales_Price__c]

        )

    )

)

/

SUMX (

    DISTINCT ( 'Date'[Date]),

    CALCULATE (

        SUMX(

            FILTER (

               'Quote Line Items',

                'Quote Line Items'[CreatedDateOnly] = MAX ('Date'[Date])

                    &&'Quote Line Items'[Header_Product__c] = TRUE

                    &&'Quote Line Items'[Selling_Type__c] = "Hire"

            ),

            'Quote Line Items'[SBQQ__Quantity__c]

        )

    )

)

Any and all help is greatly appreciated.

[Greg_Deckler]

@ejoneslor You can often use CALCULATETABLE to get the table you want with the filters you want. Hard to be specific without sample data.

[ejoneslor]

Thank you @Greg_Deckler. In my miited previous experience, using a table won;t give me what I need. Tables calculate on load wihich means they don't interact with the slicers or the date ranges as I'd liek them too.
Is it possible to include filters from two different tables in the sam CALCULATE function.
Apologies if my description isn;t great. I'm very new to this.

[Greg_Deckler]

@ejoneslor You can use CALCULATETABLE in a measure. Basically you can potentially use it to filter the table parameter of your SUMX function for example. Works just like CALCULATE but returns a table.

[ejoneslor · Accepted solution]

Thank you @Greg_Deckler for your help. I managed to find a solution without using tables in the end. As shown below.
Your help and input was appreciated.

AveWeeklyRateWon =

SUMX (

    DISTINCT ( 'Date'[Date]),

        CALCULATE (

        calculate(SUM('Quote Line Items'[Sales_Price__c])

                                ,'Quote Line Items'[CreatedDateOnly] = MAX ('Date'[Date])

                                ,'Quote Line Items'[Header_Product__c] = TRUE

                                ,'Quotes'[SBQQ__Primary__c] = TRUE

                                ,'Opportunities'[Contract_Type__c] = "HIRE"

                                ,'Opportunities'[StageName] = "Closed Won"

        )

        )

)

/

SUMX (

    DISTINCT ( 'Date'[Date]),

    CALCULATE (

        calculate(SUM('Quote Line Items'[SBQQ__Quantity__c])

                                ,'Quote Line Items'[CreatedDateOnly] = MAX ('Date'[Date])

                                ,'Quote Line Items'[Header_Product__c] = TRUE

                                ,'Quotes'[SBQQ__Primary__c] = TRUE

                                ,'Opportunities'[Contract_Type__c] = "HIRE"

                                ,'Opportunities'[StageName] = "Closed Won"

        )

    )

)