cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
texmexdragon Regular Visitor
Regular Visitor

Sum of distinct text value

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!   

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
texmexdragon Regular Visitor
Regular Visitor

Re: Sum of distinct text value

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!

 

 

 

11 REPLIES 11
Nathaniel_C New Contributor
New Contributor

Re: Sum of distinct text value

Hi @texmexdragon ,

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 New Contributor
New Contributor

Re: Sum of distinct text value

Count late1.PNGCount late.PNG

Nathaniel_C New Contributor
New Contributor

Re: Sum of distinct text value

@texmexdragon ,

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

 

 

.Count late2.PNG

texmexdragon Regular Visitor
Regular Visitor

Re: Sum of distinct text value

 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. 

 

 

texmexdragon Regular Visitor
Regular Visitor

Re: Sum of distinct text value

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. 

Nathaniel_C New Contributor
New Contributor

Re: Sum of distinct text value

Hi @texmexdragon ,

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

 

 

Super User
Super User

Re: Sum of distinct text value

Hi,

Does this measure work?

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

texmexdragon Regular Visitor
Regular Visitor

Re: Sum of distinct text value

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.    

 

 

Super User
Super User

Re: Sum of distinct text value

Hi,

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 126 members 1,741 guests
Please welcome our newest community members: