Measure to Filter and Aggregate Across 2 Fact Tables

[russell80]

Looking for some help with this one. I have a simple model where I have a table of projects, called 'Projects', where [Name] is unique, and this is related to the 'Budgets' and 'Invoices' tables with one-to-many relationships. The 'Result' i am trying to get is a table visual for every 'Projects'[Name] with a measure, [Invoiced], which shows a sum of 'Invoices'[Value] for each 'Projects'[Name], for those projects that have a sum of 'Budgets'[Value] greater than 0. Hopefully that makes sense.

 I got as far as the code below which works for each row in table visual, but the total is off and I can't get this one figured out. I tried adding the ALL filter to CALCULATE to restore it for the Total but that's obviously not working.

Invoiced =
CALCULATE (
    SUM ( 'Invoices'[Value] ),
    FILTER ( 'Budgets', SUM ( 'Budgets'[Values] ) > 0 ),
    ALL ( 'Budgets'[Name] ),
    CROSSFILTER ( 'Budgets'[Name], 'Projects'[Name], BOTH )
)

[smpa01 · Accepted solution]

@russell80  can you try this

Measure =
VAR _filterTbl =
    SUMMARIZE (
        FILTER (
            Budgets,
            CALCULATE ( SUM ( Budgets[Value] ), ALLEXCEPT ( Budgets, Budgets[Name] ) ) > 0
        ),
        Budgets[Name]
    )
RETURN
    CALCULATE ( SUM ( Invoices[Value] ), TREATAS ( _filterTbl, Invoices[Name] ) )

[russell80]

Thanks @amitchandak . I gave that a go and it works for each row in the result, but the total is not right. I think for the total, you don't have the filter context to be able to sum the budgets for each project, so all the budgets are summed which comes back as a true condition for the "> 0" filter.

[amitchandak]

@russell80 , These was small mistake, home you have forced row context using values and sumx

example or return 

sumx(filter(Values(Project[project]), _bud >0),_inv)

or

sumx(summarize(Project,Project[project],"_1",_inv, "_2" _bud), if[_2]>0 ,[_1], blank() )

[russell80]

Thanks @amitchandak . I tried both options, see below but I still don't get the correct total

[amitchandak]

@russell80 , Try a measure like 

Measure =
var _inv = calculate(sum(Invoices[Value]))
var _bud = calculate(sum(Budget[Value]))
return
sumc(filter(Values(Project[project]), _bud >0),_inv)