Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Distinct count

Hello  - I am trying to get a "distinct count" of the two different values in the calculated column below.    More specifically, I want a distinct count of the Order # associated with each line that contains one of those values.  

 

Zero Value = IF('Flu_Shipped Orders'[Shipped Price]=0,"Zero Value", "Rev Order")
 
For example, if there were 35 rows....with 5 distinct Order #'s that were "Zero Value"....then the count would be 7.     (7 rows each  of 5 distinct order numbers).     
 
The following measure works, but it is counting each distinct row....what I really need is a distinct count, based on Order number. 

 

Count of Rev Orders = COUNTX(FILTER('Flu_Shipped Orders','Flu_Shipped Orders'[Zero Value]="Rev Order"),'Flu_Shipped Orders'[Order])
 
The idea is that these two values would be compared in a stacked column chart, to compare the distinct count of Orders that were zero value orders  (returns)   versus   revenue orders.   
 
 
Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous try this

     

    Count of Rev Orders = CALCULAE ( DISTINTCOUNT( 'Flu_Shipped Orders'[Order] ), 'Flu_Shipped Orders'[Zero Value]="Rev Order")

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    Hi Anonymous ,

    I hope I have understood you correctly, then this example may answer your question:

     

     

    Regards FrankAT

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ，
    According to your description, it seems that you want to get distinct counts based on different values, right?
    You could refer to FrankAT ‘s approach. I'm just adding some additional instructions by using DISTINCTCOUNT() when there are duplicate data.

    Distinct Non-0 =
CALCULATE (
    DISTINCTCOUNT ( 'Shipped Order Table'[Shipped Price] ),
    ALLEXCEPT ( 'Shipped Order Table', 'Shipped Order Table'[Order] ),
    'Shipped Order Table'[Shipped Price] > 0
)

     

     

    [Distinct =0] =
CALCULATE (
    DISTINCTCOUNT ( 'Shipped Order Table'[Shipped Price] ),
    ALLEXCEPT ( 'Shipped Order Table', 'Shipped Order Table'[Order] ),
    'Shipped Order Table'[Shipped Price] = 0
)

    My example visualizations based on distinct counts look like this:

    Is this result what you want? If you have any questions, please upload some data samples and expected output.
    Please do mask sensitive data before uploading.
     
    Best Regards,
    Eyelyn Qin