cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MJB2017
Frequent Visitor

Counting with Criteria - formula issue

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Counting with Criteria - formula issue

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

1.PNG

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
MJB2017
Frequent Visitor

Re: Counting with Criteria - formula issue

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 Helper I
Helper I

Re: Counting with Criteria - formula issue

hi, try

 

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

Community Support
Community Support

Re: Counting with Criteria - formula issue

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

1.PNG

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors