Counting unique data based on twi columns

[ahmedabdelsaboo]

I am working with 2 columns in Power Bi. First column is (unique ID) Column and the other is (disposition ) column. The disposition column has 3 categories ( Sold, In warehouse, or In transit )

 My current table has multiple records for IDs and sometimes one unique ID may have different dispositions at different dates. E.g Unique ID 123 Sold at 11:35AM Unique ID 123 Sold at 12:35 AM & Unique ID 123 went to warehouse at 1:35 AM

I would like to be able to how many  units sold , in warehouse or in transit only once per each serial number

Is there anyone that can help me to come up with a formula to record only once how many unique IDs were sold, in warehouse or in transit

[v-yulgu-msft]

Hi @ahmedabdelsaboo,

Suppose your table structure looks like:

You could create a calculated column using below formula:

Total Sales =
CALCULATE (
    DISTINCTCOUNT ( 'Product Table'[ID] ),
    ALLEXCEPT ( 'Product Table', 'Product Table'[disposition] )
)

Or, you can create a measure using this formula:

Total Sales measure = DISTINCTCOUNT('Product Table'[ID])

Use a table visual to display data, both above methods can return this result:

Alternatively, you can directly use a matrix to show above results. 

Best regards,
Yuliana Gu

[ahmedabdelsaboo]

Thanks a lot for your help

[ahmedabdelsaboo]

@v-yulgu-msft Thanks a lot for your help