Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dakins
Helper II
Helper II

Count of products per Invoice number

Hello people,

I have a table that shows invoice number and every item purchased by the cutomer. See sample table below.

I would like to create a column or measurement that shows me count of items per invoice number.

I have another table that has unique invoice numbers

 

Thank you!

 

Invoice numberItem sold
1001Water
1001Bread
1001Coke
1002Banana
1003Pears
1003Apple
1004Tylenol
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1679112031576.png

 

 

Jihwan_Kim_0-1679112005259.png

 

 

count items: = 
COUNTROWS( DISTINCT( Sales[Item sold]) )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

The measure can be as simple as =DISTINCTCOUNT(Data[Items sold]).  Create a relationship (Many to One and Single) from the Invoice column of the Data table to the Invoice column of the Invoice table.  To your visual, drag Invoice number from the Invoice table. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you Ashish!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1679112031576.png

 

 

Jihwan_Kim_0-1679112005259.png

 

 

count items: = 
COUNTROWS( DISTINCT( Sales[Item sold]) )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you Jihwan_Kim

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.