Sum of distinct text value

[Anonymous]

Hello -  

This is probably an easy one, but I've tried various distinct count, count rows, etc and nothing has worked yet.  

I have a Sales Orders table.   In that table is the Order #  and a new calculated column that identifies the order as either late, or not late, based on a DateDiff formula (from another column).    If the due date is yesterday or beyond...then the Order is late  (because it has missed its ship date.  

I can get a toal count of all rows by using this.    But this sums to 108 which is summing up all of the orders (and some orders have multiple lines).   

Total Late =
CALCULATE ( COUNTROWS (Operations_Backlog), Operations_Backlog[Late or Not Due Yet] = "Late" )

The value I am looking for should be 64....which is the distinct order count of late orders.    (Just a coincidence that it happens to be half of the 108).

I need a formula (measure) that gives me the true total of late orders, based on the unique Order #.  

Thanks!   

[Nathaniel_C]

Hi @Anonymous ,

Try this (down and dirty) It returns the expected count.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

m3 = CALCULATE(COUNT('Order'[Status]),'Order'[Status]="Late")

[Nathaniel_C]

[Nathaniel_C]

@Anonymous ,

But then I go back and use your formulation. Mine still works correctly as expected. No wonder you are pulling your hair out. So, I wonder if it is either the column formula.  Maybe put a test column in that is an if and puts 1 for late, 2 for everything else, the try a count where it equals  1.  Worth a try...next I would wonder if there might be a relationship issue... But this definately works for a simple table as expected.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

.

[Anonymous]

 Hi Nathaniel, Still does not solve me issue unfortunately.   I will try to explain again: 

I have a column called Late or Not.   That column is derived from another column, and if the date value is less than today it assigns either "Late"  or  "Not Late" in the column.  

In my Orders table, an Order might have two lines.   Each line contains a different line item from our ERP.   

Order # 2345  Line 1    Late

Order # 2345  Line 2    Late

So, if this order, based on the due date, was Late (has not shipped on time), then it my calculated column shows it as Late.    So, in this example, the number of "late" orders is  ONE.    Not TWO.      My current measure is calculating it as TWO, which is not what I need. 

[Ashish_Mathur]

Hi,

Does this measure work?

=CALCULATE(DISTINCTCOUNT('Order'[Status]),'Order'[Status]="Late")

[Anonymous · Accepted solution]

Hi Everyone:   I got the measure to work by using this: 

New Measure = CALCULATE(DISTINCTCOUNT(Operations_Backlog[Order]),Operations_Backlog[Late or Not Due Yet]="Late")

Works perfectly. And I created another meausure, just changed "Late" to "Not Due Yet" and so I now I can track both using a measure instead of having to place a filter visual on my page.

Thanks Everyone!! Love the Power Bi community forum...always so helpful!

[Anonymous]

Hey Guys - Appreciate all the help to try and solve this!   

The solutions provided still just give me the total count of all Orders with the "Late" status.   

The Late or Not Late column is a separate column in the same table as the Orders.  

Again, the best way I can illustrate is, if these 4 columns said: 

Order #1234    Line 1   Product A   Late

Order #1234    Late 2   Product B   Late

Order #1234    Late 3   Product C   Late

Order #4655    Late 1   Product A   Late

The total number of late orders is 2, because that is the "distinct count" of order numbers.    The solutions provided so far are totaling 4 because they are counting all of the Orders.    

[Nathaniel_C]

Just add Line 1 to your formula

= CALCULATE(COUNT('Order'[Status]),'Order'[Status]="Late", 'Order' [Line] ="Line 1")

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel 

[Ashish_Mathur]

Hi,

I am quite certain that my formula will work.  Share the link from where i can download your PBI file.

[Nathaniel_C]

Hi @Anonymous ,

So are these in one column, or is the line order represented by a different column?  If indeed it is in one column, then you can't differentiate between 2 orders and 1 order with 2 lines.

So if I am correct in this description, one solution would be to  extract the text that contains only the order number and your count would work.  I did that for someone today already. You just need to know what the number of characters to extract, and where  to begin. 

Let me know if you have any questions.  I would do this in Power Query as there is less impact on performance.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

And to add one more thing. If I use your measure, or even mine, and I put a filter on the visual, Late  or  Not Late, and select Late, it gives me the correct value. 

But I am trying to accomplish the same thing, in a measure, where no added visual filter is needed.