Convert sumproduct formula for use in powerpivot?

[rskadk]

I'm looking for a formula for use in powerpivot, that can convert the following formula (I do not wanna use any VBA, only generate a caluculated column in powerpivot, based on a formula)

=IF(SUMPRODUCT(([Item]=1234)*([Order]=A2))>0;"YES MATCH = 1234";IF(SUMPRODUCT(([Item]=1857201)*([Order]=A2))>0;"YES MATCH = 1857201";IF(SUMPRODUCT(([Item]=1857206)*([Order]=A2))>0;"YES MATCH = 1857206";"NO MATCH")))

The formula checks for 3 different values, and returns a status code for each. If no match is found it returns "no match".


The formula looks for the criteria (1234 or 1857201 or 1857206) in column2 (item) and writes a status code in column3 (output). At the same time it also checks for simular order numbers (column1). So the same status code is present for all order-number rows. (If a match in column2 / item is found)


Se the excample in regular excell here:

http://www.filedropper.com/book3

 

[rskadk]

I'm playing around with this formula

=IF(SUMX(FILTER(Table1;[Item]=1234);[Order])=[Order];"YES 1234";"NO MATCH")

It works for "1234", but if i extend the above formula with IF statements, to combine it with the other 2 criterias (1857201 or 1857206), i just get "NO MATCH"... Maybe the above formula can be used as an inspiration

See below, with the above inserted formula

View image: Capture

 

[scottsen]

If we only cared about a single row, we would just do...

=IF(Table1[Item] = 1234 || Table1[Item] = 1857201 || Table1[Item] = 1857206, "Yes Match", "No Match")

And you could still do that, then build another column based off that for your "final answer".

I think the "all in one" is something like this:

<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1 <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span>Table1,<br><span class="indent8">        </span>Table1[Order] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[Order] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="indent4">    </span>&&<span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[Item] = <span class="Number" style="color:#EE7F18">1234</span><br><span class="indent8">        </span><span class="indent4">    </span>|| Table1[Item] = <span class="Number" style="color:#EE7F18">1857201</span><br><span class="indent8">        </span><span class="indent4">    </span>|| Table1[Item] = <span class="Number" style="color:#EE7F18">1857206</span> <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

Errr, well, you would wrap that in an IF for when the count > 0 I suppose...

 

[rskadk]

If we only cared about a single row, we would just do...

=IF(Table1[Item] = 1234 || Table1[Item] = 1857201 || Table1[Item] = 1857206, "Yes Match", "No Match")

And you could still do that, then build another column based off that for your "final answer".

I think the "all in one" is something like this:


CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& ( Table1[Item] = 1234
|| Table1[Item] = 1857201
|| Table1[Item] = 1857206 )
)
)


Errr, well, you would wrap that in an IF for when the count > 0 I suppose...

I tried your formula, but i cannot get it to work. :/ Is it possible you can insert it in the formula field, in the linked file in #1? Basically i just need to excel to generate the same value in powerpivot as when using sumproduct in reguarly excel.

 

[rskadk]

I need to correct the abobe answer to your post. I can get the formula working, returning "1", if there is a match for either 1234, 1857201, 1857206. But if i wanna combine it with an IF. And returning 3 different answers, based on each item-number. How can this be done?

Ex.

1234 = YES MATCH 1234
1857201 = YES MATCH 1857201
1857206 = YES MATCH 1857206

All other = NO MATCH

 

[scottsen]

Looks I was missing a paren at the end? I added the IF check, does this solve your problem?

=<br><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1 <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="indent4">    </span>Table1,<br><span class="indent8">        </span><span class="indent4">    </span>Table1[Order] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[Order] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="indent8">        </span>&&<span class="Parenthesis" style="color:#D0D0D0"> (</span> Table1[Item] = <span class="Number" style="color:#EE7F18">1234</span><br><span class="indent8">        </span><span class="indent8">        </span>|| Table1[Item] = <span class="Number" style="color:#EE7F18">1857201</span><br><span class="indent8">        </span><span class="indent8">        </span>|| Table1[Item] = <span class="Number" style="color:#EE7F18">1857206</span> <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="StringLiteral" style="color:#D93124">"Yes Match"</span>,<br><span class="indent4">    </span><span class="StringLiteral" style="color:#D93124">"No Match"</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

 

[scottsen]

I need to correct the abobe answer to your post. I can get the formula working, returning "1", if there is a match for either 1234, 1857201, 1857206. But if i wanna combine it with an IF. And returning 3 different answers, based on each item-number. How can this be done?

Ex.

1234 = YES MATCH 1234
1857201 = YES MATCH 1857201
1857206 = YES MATCH 1857206

All other = NO MATCH

Ah! I will have to look later this evening. What should happen if multiple match?

 

[scottsen]

Written mobile while wife drives to movie, but I think this inelegant solution should work.


=
IF (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& Table1[Item] = 1234
)
), "Match 1234",
IF (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& Table1[Item] = 1857201
)
), "Match 1857201",
IF (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& Table1[Item] = 1857206
)
), "Match 1857206",
"No Match")))

 

[rskadk]

Written mobile while wife drives to movie, but I think this inelegant solution should work.


=
IF (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& Table1[Item] = 1234
)
), "Match 1234",
IF (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& Table1[Item] = 1857201
)
), "Match 1857201",
IF (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
Table1[Order] = EARLIER ( Table1[Order] )
&& Table1[Item] = 1857206
)
), "Match 1857206",
"No Match")))

Perfect - this works great. Thank you for the solution provided

I don't know excactly how the multible match should work. In the formula you provided, how would the output be if multible match. Due the IF-contruction, would it just use the first match? Ex. if "1234" or "1857201" is present with the same [Order] is present, then it will use "1234"?

 

[scottsen]

Ya, it is going to work the same as as your original xl formula. 1234 is the favorite.