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

It looks like this is a known issue for Microsoft...They've apparently been aware of it since Excel 2003. See this [link](https://support.microsoft.com/en-us/help/211470/calculated-field-returns-incorrect-grand-total-in-excel). — Marc Pincince, Sep 05 '17 at 15:50
Hi Marc, thanks for your comment. However the article is about Pivot Tables in general. My problem should be solvable within **Power Pivot**. Best, Gökhan — Gökhan Acar, Sep 05 '17 at 23:50

Marc Pincince · Answer 1 · 2017-09-06T14:43:16.300

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:

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

Entity Invoice Lease

Entity Invoice Lease Measure

1 Answers1