# Sumproduct formula converted to DAX for PowerPivot



## JV0710 (Feb 7, 2013)

Hi All

I am fairly new to powerpivot, so please bear with me for this question - 

I have a sumproduct formula which I want to use in my powerpivot table and need to convert it to a dax formula.

The sumproduct formula is: 
=IF(SUMPRODUCT(--($C$2:$C$75000=C2)*($F$2:$F$75000<F2)*($H$2:$H$75000))=0,"New Supplier", "Existing Supplier")

Please can I get some help to rewrite this for a powerpivot table

Thank you

JVN


----------



## AlbertoFerrari (Feb 7, 2013)

Well, on the other hand, I am not so skilled in Excel, never knew about the very existence of SUMPRODUCT. 
Anyway, I answer to give some hints about the most complex part of your problem, which is making the product of many rows, while I leave the exact formulation of the expression to somebody who has a better understanding of how SUMPRODUCT works.
In DAX, there is no way to multiply a set of rows, there is no MULTIPLYX function. But, thanks to logarithm properties, you can author the formula using SUMX, EXP and LN.
In this old thread you will find a complete example: http://www.mrexcel.com/forum/powerp...mpting-calculate-product-previous-values.html, there are comments of mine and some other clarification that are worth reading.
Hope this helps.

Alberto
SQLBI


----------



## JV0710 (Feb 7, 2013)

Thank you for your feedback I will check the link that you posted

To Everyone

I apologise for any confusion - I did not pick up that the sumproduct formula I osted was incomplete - below is the correct formula

=IF(SUMPRODUCT(--($C$2:$C$75000=C2)*($F$2:$F$75000<F2)*($H2:$H$75000))=0,"New Supplier","Existing Supplier")

Thanks

Joe


----------



## JV0710 (Feb 7, 2013)

Let's try that again  - - - Apologies once more ...

=IF(SUMPRODUCT(--($C$2:$C$75000=C2)*($F$2:$F$75000<F2)
*($H$2:$H$75000))=0,"New Supplier","Existing Supplier")

Please can I get some help to rewrite this for a powerpivot table


----------



## miguel.escobar (Feb 7, 2013)

Hey!

So you're using boolean with SUMPRODUCT within an IF statement...pretty cool !

Do you need to rewrite this as a calculated column or as a measure? 

Let me throw an idea on how it would look like

=IF( SUMX( FILTER(Table, $C$2:$C$75000=C2),  $F$2:$F$75000 * $H$2:$H$75000 ) = 0, "New Supplier", "Existing Supplier")

$C$2:$C$75000 = a column name within the filter
C2 = an specific value  within the column $C$2:$C$75000
$F$2:$F$75000 = a column that you're trying to sumproduct against $H$2:$H$75000
$H$2:$H$75000 = a column that you're trying to sumproduct against $F$2:$F$75000

it goes something among those lines.

Hope it helps.


----------



## JV0710 (Feb 7, 2013)

Hi Miguel

I am thinking that I will need to write this as a calculated column.

Once I have the suppliers marked in the column as "new" or "Existing"  - I will be able to use a slicer in my Pivot Table to view accordingly. I am also then going to be counting "new" vs "Existing" for specific time periods etc.

Thanks for your suggestion 

I will give it a try and let you know the final outcome

Joe


----------



## miguel.escobar (Feb 7, 2013)

Let me know if you need anything else. If you have a workbook that you could share I'd love to take a look at it.

Thanks!


----------



## JV0710 (Feb 8, 2013)

Hi All

Query sorted - Thanks for your contribution

The final formula used, after much searching and testing,  was:

=IF(CALCULATE(MIN(Query[Season number]), ALLEXCEPT(Query, Query[DimorderSupplierSupplier]))=
CALCULATE(MAX(Query[Season number]), ALLEXCEPT(Query, Query[DimorderSupplierSupplier])),"New", 
IF(CALCULATE(MIN(Query[Season number]), ALLEXCEPT(Query, 
Query[DimorderSupplierSupplier]))=Query[Season number],"New", "Existing"))

***********************************************************************
The original Sumproduct formula that works in Excel and had to be re-written as DAX, is below

“  =IF(SUMPRODUCT(--($C$2:$C$75000=C2)*($F$2:$F$75000 < F2)*($H$2:$H$75000))=0,"New Supplier","Existing Supplier")  “


----------



## stoudamire (Nov 24, 2013)

Hi all,

I am also quite new to powerpivot and I have to rewrite a similar formula.

=IF(SUMPRODUCT(--(PO-Number=C2),--(B2>Scan-Date))+1=1,"YES","NO")

Any idea how I can do this with DAX?

Thanks in advance for your help,
Dirk


----------

