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
Highlighted
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?

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
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

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.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors