cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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 3
Highlighted
Microsoft
Microsoft

Re: Counting unique data based on twi columns

Hi @ahmedabdelsaboo,

 

Suppose your table structure looks like:

1.PNG

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:

2.PNG

 

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

4.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
New Member

Re: Counting unique data based on twi columns

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

Highlighted
New Member

Re: Counting unique data based on twi columns

Thanks a lot for your help

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors