cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahmedabdelsaboo
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
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.
ahmedabdelsaboo
New Member

Re: Counting unique data based on twi columns

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

ahmedabdelsaboo
New Member

Re: Counting unique data based on twi columns

Thanks a lot for your help

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors