Power Pivot: sum column a if sum column b > 0

Question

I am at my wit's end regarding the following table entries within a power pivot model and the targeted measure/result.

We have the following data:

Entity Invoice Lease

 1            15       14
 1            20       20
 1           100
 2            50
 2            75
 3            20       10
 3            30
 3            50

Now I would like to add a measure which gives us only the sum of Inovice, if the sum of Lease is > 0. The pivot table should look like:

Entity Invoice Lease Measure

 1          135         34        135
 2          125                     0
 3          100         10        100

Thank you for all hints and solutions.

Best Gökhan

Answer 1

I think I may have a solution for you...

Start with this table in Excel, named Table1:

...and add it to your data model:

Then add a column with this code:

=if(AND(MAXX(filter(Table1,[Column1]=EARLIER([Column1])),[Column3])>0,minx(filter(Table1,[Column1]=EARLIER([Column1])),[Column3])>0),SUMX(filter(Table1,[Column1]=EARLIER([Column1] )),[Column2])/COUNTX(filter(Table1,[Column1]=EARLIER([Column1] )),[Column2]),0)

...to get this:

Then add a Pivot Table with these settings:

...to get this: