Counting with Criteria - formula issue

[MJB2017]

Dear All,

I'm hoping someone can offer me some advice on a number I'm trying to identify. To better explain this in context let me show you a sample table:

Order Number:       Pick Up Status:               Completeion Status:
1111                        Shipped                         Complete

1122                        Shipped                         Not Complete
1111                        Yet to be shipped          Not Complete

1133                        Shipped                         Complete

1111                        Shipped                         Not Complete
1112                        Yet to be shipped         Not Complete

I'm trying to identify the DISTINCT NUMBER of order numbers (PO's in my table) that have been shipped, but are not complete (meaning they've not reached their destination).

The formula I'm currently using is:

PO's in Transit =
COUNTROWS(
FILTER(
SUMMARIZE(
'Forwarder Table','Forwarder Table'[PO],"Shipped",CALCULATE(
DISTINCTCOUNT('Forwarder Table'[PO]),
FILTER(
'Forwarder Table',
NOT CONTAINS('Forwarder Table','Forwarder Table'[Completion],"No")
)
)

),
[Shipped]=1
)
)

Now, using an excel sumproduct/countif formula, along with a bit of manual work, I can identify the correct number.

For some reason on Power BI I'm getting a significantly higher number. As I'm now doing all my reporting on Power BI (it's amazing, but I'm new at this). This table is only about 10,000 cells long, but it's growing quickly. Would really appreciate it if someone could take a look and let me know where I'm going wrong in my formula?

Cheers!

M

[v-shex-msft · Accepted solution]

Hi @MJB2017,

According to your description, you want to calculate the shipped but not completed records, right?

If this is a case, you can refer to below formula.

Measure:

PO's in Transit =
CALCULATE (
    DISTINCTCOUNT ( 'Forwarder'[Order Number] ),
    FILTER (
        'Forwarder',
        [Pick Up Status] = "Shipped"
            && [Completeion Status] = "Not Complete"
    )
)
    + 0

Regards,

XIaoxin Sheng

[MJB2017]

Just a quick update. I managed to get the number I wanted by:

1. Creating a custom Column using the IF function for Completion and Pick Up Status:

Shipped & Not Complete = IF(AND('Forwarder Table'[Completion]="No",'Forwarder Table'[Forwarder Pickup Status (If Any)]="Shipped"),1,0)

2. I then just created a simple measure using Calculate and Distinct Count to select the PO's linked with either of those two criteria:

PO's in Transit = CALCULATE(DISTINCTCOUNT('Forwarder Table'[Po]),'Forwarder Table'[Shipped & Not Complete]=1)

I'm still cruious if anyone has a better solution using a formula as opposed to introducing a new column?

[mechanix85]

hi, try

DISTINCTCOUNT Order Number:=CALCULATE(DISTINCTCOUNT([Order Number:]);'table'[Pick Up Status:]="Shipped";'table'[Completeion Status:]="Not Complete")