Forum Discussion

ahmedabdelsaboo's avatar
ahmedabdelsaboo
New Member
9 years ago

Counting unique data based on twi columns

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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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