PowerPivot COUNTIFS question

[Reshaw]

Firstly I have read several other posts on here that appear to answer very similar questions to the one i'm about to pose, but I am absolutely brand new to PowerPivots and the data manager model. I am very familiar with excel but have approximately 3 hours experience with power pivots.

What I am trying to do is fairly simple but I can't figure out what i'm doing wrong, the various you tube videos seem to suggest this is really easy too...

The task: I have a list of locations in a warehouse in one table and a second table containing records of when an inventory check has been done.

There are literally 10's of thousands of locations so I summarise them by Aisle Description, either loa_id or left(loc_id, 2) achieves the same thing (this is the location table below):

whs_id	loa_id	loa_description	lct_id	loc_id
Main	AA	Aisle AA	Bulk	AA01A
Main	AA	Aisle AA	Bulk	AA01B
Main	AA	Aisle AA	Bulk	AA01C
Main	AA	Aisle AA	Bulk	AA01D
Main	AA	Aisle AA	Bulk	AA01E
Main	AA	Aisle AA	Bulk	AA01F
Main	AA	Aisle AA	Bulk	AA02A
Main	AA	Aisle AA	Bulk	AA02B
Main	AA	Aisle AA	Bulk	AA02C
Main	AA	Aisle AA	Bulk	AA02D
Main	AA	Aisle AA	Bulk	AA02E
Main	AA	Aisle AA	Bulk	AA02F

In the inventory check table, it only has the individual location but i can calculate the aisle description using the left(loc_id, 2) again.
I then count the various outcomes of the inventory checks in the pivot table and i need to calculate a % complete figure.

[Total Checks in that aisle] divided by [total locations in the aisle]

Below is a paste of the pivot table I have so far (first few rows for brevity)

Count of pih_id	Status
loa_id	Bad Label	Cancelled	Matched	Missing / Empty	No Label	Not counted	Over	Short	Grand Total
AA	30	21	2851	37	34		35		3008
AB	6	9	1646	15			12		1688
BA	15	16	2347	13	16	1	12		2420
BB	10	4	1715	18			18		1765

I want to add a calculated field that goes to the locations table and counts how many rows it finds for a given aisle description. so say a given aisle has 500 location sin it, i want to divide the grand total on the pivot above by the total locations.

I have tried without success to use the countrows function both as a calculated field in the data manager and as a measure at the bottom, not made either work thus far, this was the calculated column attempt which does not work:

=calculate(countrows(rlv_STOCK_Location_list[loa_id]), rlv_ITPL_PerpetualInventory_Detail[Aisle_Desc]) )

I have also tried this:

=countx(rlv_STOCK_Location_list, rlv_ITPL_PerpetualInventory_Detail[Aisle_Desc])

which counts all the locations and doesn't match on the Aisle description and then crashes the spreadsheet!

Please help this old dog learn a new trick!

Thanks in advance

 

[macfuller]

You may want a measure rather than a calculated column for greater flexibility.

You don't provide the structure of the inventory table so it's hard to know exactly how the two tables relate. I assume there is some way for the inventory table to show the warehouse and aisle, not just the location, since you need to differentiate between aisle 5 and bin 3 in one warehouse vs. another. Another question is product - would aisle 5/bin 3 in one warehouse have the same widget as that location in another? (I assume not, but it depends how your WMS functions.)

So first I would suggest creating a primary/foreign key relationship to relate the location file to the inventory file. Are you just interested at what happens at the aisle level and not the bin level? Are each of the "checks" performed the values in the 2nd row of your result (Bad Label, Cancelled, Match etc)? I think we need to see the structure of your inventory table - are those columns in the table, or values of a single "Check" column?