Distinct count

[Anonymous]

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.   

[Anonymous]

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

[FrankAT]

Hi @Anonymous ,

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

Regards FrankAT

[parry2k]

@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.⚡