Counting failing deliveries - supply chain

[Anonymous]

Hi Everyone,

I could really use some expert help! 
We have a report to monitor and analyse all open orders within our department. In this report we designed some predictive indicators to tell us if an order is likely not to be delivered on time. The report is refreshed everyday, and we would like to track the progression of these various indicators. We plan to have a table visualization listing all the indicators we have in place and I would need to create a measure to count how many unique sales items are affected. Here are the rules:
The indicators are in a separate calculated table and calculated by line item.
If one line item tests true for an indicator all items in the same sales order on the same shipping point should be considered as lost and counted.
So my question is: how do I select the shipping point and sales order number in the given row to use as filters for the count function, and how do I avoid duplicate calculations. Below is some sample data.

We count the unique sales items.
For “out of stock” we should get 3 as a result, since we have 5 lines, and only 1 item is affected but the other 2 on the same shipping point will also be lost. 

“Damaged Package” should be 1
“Overdue bill” should be 2 because there are 4 lines on the same shipping point, but only 2 unique items.

I know it might sound a bit confusing, but I hope it can be done and some of you will be able to help me.
Thank you!

[v-gizhi-msft]

hi,

According to your description, Please try this measure:

Count of Indicator =

var summry=SUMMARIZE(FILTER('Table','Table'[Indicator example]<>BLANK()),[ShippingPoint],[SalesOrder],[ProductCode],[Indicator example],"Con",[ShippingPoint]&","&[SalesOrder])

return

COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED('Table'),[ShippingPoint],[SalesOrder],[ProductCode]),[ShippingPoint]&","&[SalesOrder] in SELECTCOLUMNS(summry,"Con",[Con])))

This is my test table:

The result shows:

Here is my pbix file.

pbix 

If you still have questions about it, please for free to let me know.

Best Regards,

Giotto Zhi

[Anonymous]

Hi Giotto,

Thank you very much for taking the time!

This solution is very promising, however I think it does not take into account the DeliveryDate. The unique sales item is determined by SalesOrder&ProductCode&DeliveryDate. The numbers your measure return are correct, but I think if we change the dates the result would not be correct.

With a small change on SaleOrder 1111 we should still get 3

https://drive.google.com/open?id=1cHX1bnIVKlGJBZo2hWy5CCknJxJi3bid

[v-gizhi-msft · Accepted solution]

hi,

According to your description, please change the original measure I provided last time to this:

Count of Indicator =

VAR summry =

    SUMMARIZE (

        FILTER ( 'Table', 'Table'[Indicator example] <> BLANK () ),

        [ShippingPoint],

        [SalesOrder],

        [ProductCode],

        [Indicator example],

        "Con", [ShippingPoint] & "," & [SalesOrder]

    )

RETURN

    COUNTROWS (

        FILTER (

            SUMMARIZE (

                ALLSELECTED ( 'Table' ),

                [ShippingPoint],

                [SalesOrder],

                [ProductCode],[DeliveryDate]

            ),

            [ShippingPoint] & ","

                & [SalesOrder] IN SELECTCOLUMNS ( summry, "Con", [Con] )

        )

    )

I change the data like you:

The following shows the right result as the same with that result before [DeliveryDate] being changed:

Here is my pbix file.

pbix 

If you still have questions about it, please for free to let me know.

Best Regards,

Giotto Zhi