Qty items counted accross multiple bins

[Anonymous]

Hello

I have 5950  items in a warehouse spread accross 9657 bins. At the moment the calculations show how many bins have been counted, how do I calculate how many items have been fully counted? 

So 1 item may have 20 bin locations but only 19 of those locations may have been counted therefore the item is not fully counted, but if another item has 20 bin locations and all of those locations have been counted then the item is fully counted. How do I show the qty of items which have all it's bins counted?

Thanks 

Bailey14

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi

Not sure if this would help? This particular item has several different locations but not all the locations have been counted, therefore the item is not fullt counted. See 'counted' column

[Icey]

Hi @Anonymous ,

Please check if this is what you want:

Is Counted All Bins Measure =
VAR ItemBins_ =
    CALCULATE (
        COUNT ( 'Table'[Bin Code] ),
        ALLEXCEPT ( 'Table', 'Table'[Item No.] )
    )
VAR CountedItemBins_ =
    CALCULATE ( SUM ( 'Table'[counted] ), ALLEXCEPT ( 'Table', 'Table'[Item No.] ) )
RETURN
    IF ( ItemBins_ - CountedItemBins_ = 0, 1 )

qty of items which have all it's bins counted Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Item No.] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Is Counted All Bins Measure] = 1
    )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Hello Icey

I have tried the measure but I don't really understand what tables should go where within it,

I'm expecting to see this

MDC = 2870 counted

UK web  = 3544 counted

IE web = 363 counted

But I'm getting this

So I'm sure I'm not putting the right tables into your measure

This is how I've done it

Here is the link to the file

[Icey]

Hi @Anonymous ,

Try this: don't use ALLSELECTED in the below measure:

qty of items which have all it's bins counted Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Item No.] ),
    FILTER (
        'Table' ,   -------------------------------------don't use ALLSELECTED()
        [Is Counted All Bins Measure] = 1
    )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Hello again

I think we're getting nearer to the solution, I'm getting these results now which are better but not quite the number I am expecting

Web UK should be 4493

MDC should be 2874

Web IE is correct at 364

The difference between the WEB IE warehouse is that there is only 1 bin per item, the other two warehouses can have multiple bins per item. 

These are the measures which are in place now

Kind Regards

Bailey14

[Icey]

Hi @Anonymous ,

If so, how about using COUNT instead of DISTINCTCOUNT?

qty of items which have all it's bins counted Measure =
CALCULATE (
    COUNT ( 'Table'[Item No.] ),                 -----------Use COUNT
    FILTER (
        'Table' ,   
        [Is Counted All Bins Measure] = 1
    )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous · Accepted solution]

Hi Icey

Unfortunately it's not worked, however I've created a seperate table for each warehouse using a pivot table to create the correct values.

I really appreaciate your time trying to fix this though.

Kind regards

Bailey14