PowerPivot calculated field question

[edusuro]

Hi,

I am trying to include a clause on a calculated field so that it displays the max between the formula that I already have in the calculated field and a number on a table which includes target minimums.

The issue is that I am not able to link the minimums table to any other table since it can't be linked to any of the other tables due to multiple fields being needed.

Is there anyway that I could use something similar to a vlookup on the calculated formula so that it looks at the header of the pivot table in that column and performs a lookup based off of that?

Thanks in advance!

 

[scottsen]

The short answer is yes For the longer answer I will probably need some more details... eg: what fields/values will you use to do the lookup?

The high level is just something like:
=IF ([MyMeasure] < [MinAllowedValue], [MinAllowedValue], [MyMeasure])

MinAlowedValue is going to depend on what you have going on...
= CALCULATE(MIN(MinsTable[Value]), FILTER(MinsTable, ???))

 

[edusuro]

The short answer is yes For the longer answer I will probably need some more details... eg: what fields/values will you use to do the lookup?

The high level is just something like:
=IF ([MyMeasure] < [MinAllowedValue], [MinAllowedValue], [MyMeasure])

MinAlowedValue is going to depend on what you have going on...
= CALCULATE(MIN(MinsTable[Value]), FILTER(MinsTable, ???))

Thanks scottsen. The high level looks like what I'm looking for.

I have a table with a format as below:
[TABLE="width: 407"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]ITEM[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Location 1[/TD]
[TD]Item 1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Location 1[/TD]
[TD]Item 2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Location 1[/TD]
[TD]Item 3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Location 1[/TD]
[TD]Item 4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Location 2[/TD]
[TD]Item 1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Location 2[/TD]
[TD]Item 2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Location 2[/TD]
[TD]Item 3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Location 2[/TD]
[TD]Item 4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]


The items are the headers on my pivot table and the location is one of the values on each of the rows of the pivot table.

I need to compare the value that is currently being generated by the calculated field that I added to my pivot table, to the appropriate value in the table above and would like the pivot table to just display the maximum between these two.

Thanks!

 

[scottsen]

Likely this will work for you, instead of resorting to your own calculate and filter:
LOOKUPVALUE Function (DAX)

Just be careful of grand total cells messin' w/ you

But if I was describing in english "how do you look up the min value"... it is by Location, Item and... Item (A..E) ?

 

[edusuro]

Likely this will work for you, instead of resorting to your own calculate and filter:
LOOKUPVALUE Function (DAX)

Just be careful of grand total cells messin' w/ you

But if I was describing in english "how do you look up the min value"... it is by Location, Item and... Item (A..E) ?

Yes, you are correct, I am looking by Location, Item and Item Type (columns). I was planning on using an IF statement to determine which column to pick from once I figure out the LOOKUP based on the other two values.

I tried the LOOKUPVALUE and think it could probably work, however, I got the following error:
Calculation error in measure 'RawUsage'[Minimum]: The value for column 'Location' in table 'TechMaster' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

This is the formula I used:
=LOOKUPVALUE(Minimum[A],Minimum[Location],TechMaster[Location],Minimum[ITEM],ItemMaster[Item Name])

Basically, the RawUsage and TechMaster are two tables being used to put together the pivot table. RawUsage includes usage of items by Techs and item IDs. RawUsage maps to TechMaster by a TechID and maps to the ItemMaster by ItemID.
Minimum is the name of the table that includes the minimums.

 

[scottsen]

Pretty much any time you see "cannot be determined in the current context", you should just think "oh. I forgot an aggregate".

Because each cell in a pivot table can have multiple values... you need to map that to just 1 value.

Try this?
=LOOKUPVALUE(Minimum[A],Minimum[Location],MIN(TechMaster[Location]),Minimum[ITEM],FIRSTNONBLANK(ItemMaster[Item Name], 1))

MIN() should look reasonable to you. FIRSTNONBLANK with ,1 as the 2nd arg is obviously weirder but MIN() doesn't work on text columns, so we use this "trick".

 

[edusuro]

Looks like we are getting there, but now I'm still getting the following error when using the formula above:

Too few arguments were passed to the FIRSTNONBLANK function. The minimum argument count for the function is 2.

 

[scottsen]

Bad copy paste!?

=<br><span class="Keyword" style="color:#0070FF">LOOKUPVALUE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span>Minimum[A],<br><span class="indent4">    </span>Minimum[Location], <span class="Keyword" style="color:#0070FF">MIN</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> TechMaster[Location] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span>Minimum[ITEM], <span class="Keyword" style="color:#0070FF">FIRSTNONBLANK</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> ItemMaster[Item Name], <span class="Number" style="color:#EE7F18">1</span> <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

 

[edusuro]

Wow!! You are a GOD!

After I re-read the error I posted, I felt really stupid. Sorry, I've been trying to figure this report out all day. I updated the formula based on your tips and it worked. I ended up using the formula below since the MIN didn't work for the location.
=
LOOKUPVALUE (
Minimum[A],
Minimum[Location], FIRSTNONBLANK( TechMaster[Location], 1 ),
Minimum[ITEM], FIRSTNONBLANK ( ItemMaster[Item Name], 1 )
)

I had an additional question to the second part of the problem. The column pulled by the lookup (column A, B, C, etc.) will vary depending on a value on another column. I used an IF statement to look at the value and then inserted the lookup function within it which worked, but I was wondering if there was a better way to do this.

Again, thank you a lot for your help today!

 

[scottsen]

Getting super hard for me to visualize, I would probably need to see a workbook or at least picture of the model.

Congrats on making progress