0

I'm essentially trying to create a new column that has a city given MAX dollars spent in a city for a given program, and that the CITY value isn't "BAD". Data example below

PROGRAM | CITY | DOLLARS SPENT | NEW COLUMN

   1    |   X  |      $20      |     ?
   1    |   Z  |      $30      |     ?
   1    |   Y  |      $40      |     ?
   1    |  BAD |      $50      |     ?
   2    |   X  |      $30      |     ?
   2    |  BAD |      $50      |     ?

So, if City = "BAD", i want the new column to return the Max City by Dollars Spent in the New Column.

Ideally the output would look like this.

PROGRAM | CITY | DOLLARS SPENT | NEW COLUMN

   1    |   X  |      $20      |     X
   1    |   Z  |      $30      |     Z
   1    |   Y  |      $40      |     Y
   1    |  BAD |      $50      |     Y
   2    |   X  |      $30      |     X
   2    |  BAD |      $50      |     X

u/tiger gave a great plain excel response. Still looking for a solution to do completely within powerpivot! Hopefully that all makes sense. Thanks!

James M.
  • 3
  • 3
  • Shouldn't the output be `{Y, Y, Y, Y, X, X}` because city Y spent the Max dollars ($40) on Program 1 excluding `BAD` and city X spent the Max dollars ($30) on Program 2 excluding `BAD`? Why are your results `{X, Z, Y, Y, X, X}`? – tigeravatar Mar 24 '16 at 18:50
  • No, I only want to change the City where City = "BAD". Where city used to be "BAD", I want it to return the largest City that wasn't "BAD" – James M. Mar 24 '16 at 18:55
  • So... If City <> "Bad" then City else Largest City? You don't want Largest City returned for all of them? Is there a particular reason why not? – tigeravatar Mar 24 '16 at 18:57
  • Yeah, the reason is just that BAD isn't an acceptable value for a City, but I don't want to reassign all of the dollars spent on the program. Therefore I'm attempting to reassign where that value isn't acceptable only. – James M. Mar 24 '16 at 19:01

2 Answers2

0

Here is a formula that does as you've described based on the sample data provided. In cell D2 and copied down:

=IF(B2="BAD",INDEX($B$2:$B$7,MATCH(1,INDEX(($A$2:$A$7=A2)*($B$2:$B$7<>"BAD")*($C$2:$C$7=MAX(INDEX(($B$2:$B$7<>"BAD")*($A$2:$A$7=A2)*$C$2:$C$7,))),),0)),B2)

It will give results like so:

enter image description here

tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • I should have added this in the title. But do you know how to do this in Poiwerpivot? No index funtion there – James M. Mar 24 '16 at 19:02
  • Unfortunately, I do not :( – tigeravatar Mar 24 '16 at 19:03
  • I was able to use your method. However due to automation limitations, I need to keep it all contained within Powerpivot formulas as that is where the data is contained. Removing the data from the powerpivot tables makes it static, which is dangerous for frequent updates. Your method is a great short term fix, but doesn't completely solve the problems at hand. Thanks again for your response, /tigeravatar! – James M. Mar 24 '16 at 23:15
0

Try this calculated column:

=IF(
  YourTable[City]="BAD",
  CALCULATE(
    VALUES(YourTable[City]),
    TOPN(
      1,
      FILTER(
       ALL(YourTable),
       YourTable[City]<>"BAD"
       && YourTable[Program]=EARLIER(YourTable[Program])
      ),
      YourTable[Dollars Spent]
    )
  ),
  YourTable[City]
 )
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • I'm getting an error with this formula, because the measure is refering directly to the column YourTable[City] without performing any aggregation on that column. Is the TOPN misplaced? – James M. Mar 25 '16 at 14:40
  • @JamesM. Did you use this as a measure of a calculated column? Try a calculated column – GregGalloway Mar 25 '16 at 17:36
  • No, I had used it as a formula directly in the calculated column. – James M. Mar 25 '16 at 17:59
  • @JamesM. apologies for the error. I've fixed a few problems in the formula. It needed VALUES around the CALCULATE(YourTable[City] section. And for some reason, ALLEXCEPT wasn't working as expected. – GregGalloway Mar 25 '16 at 20:00