cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
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?

Highlighted
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")

Highlighted
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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors