Sumproduct formula converted to DAX for PowerPivot

[JV0710]

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]

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]

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]

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]

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]

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]

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]

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]

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