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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
palak
Regular Visitor

Sum/Count of distinct column values based on another column

Hello experts,

 

I have a table with Bill # and item name as follows: 

Input.pngThe values for the column Item_Name are coming from a different table via VLOOKUP. I am trying to sum/count all the distinct items per bill

example: Bill 2 has 5 items: 3 Apple, 1 Mango, 1 Peach so the count/sum of distinct Item in Bill 2 = 1+1+1 = 3.

 

So the final result should look like the below table:

Result.png

I read various articles and references on disticnt count, count of count, but nothing has worked for me. Appreciate all your help. Thanks in Advance!

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @palak, thanks for the clarification, I misread that above! You mentioned you had issues using the distinct count function...what was the issue?

 

Based on the table in your screenshot, I would write a measure like this:

 

Distinct Items = DISTINCTCOUNT(TableName[Item_Name])

If you then put Bill # on rows, and use Distinct Items measure as the value, this should give you what you are looking for. If you still have issues, please post your .pbix file to help with troubleshooting 🙂

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @palak, just to confirm, do you always have one item per row? If so, you could use COUNTROWS(TableName), which just returns the number of rows in the table. If you then put that into a chart with Bill # on rows, you will get the number of rows per Bill #.

Hi @Anonymous, thanks for your prompt reply.

 

To answer your question; there are multiple/redundant items for the same bill #.

example:

Like lets say I went to a grocery store and bought 2 apples, 1 orange, 1 peach; I received 1 bill for this transaction. In my bill there are 4 items, but the distinct count of items will be 3. This is what I am looking to achieve.

 

Now, the problem with COUNTROWS(TableName) is that the result from this query does not give me the distinct counted values of item per bill. For the same grocery example; COUNTROWS gives me 4 rather than 3. 

Even after plotting as suggested, into a chart with Bill # on rows, the redundant count of item_name is taken.

 

Thanks,

Palak

Anonymous
Not applicable

Hi @palak, thanks for the clarification, I misread that above! You mentioned you had issues using the distinct count function...what was the issue?

 

Based on the table in your screenshot, I would write a measure like this:

 

Distinct Items = DISTINCTCOUNT(TableName[Item_Name])

If you then put Bill # on rows, and use Distinct Items measure as the value, this should give you what you are looking for. If you still have issues, please post your .pbix file to help with troubleshooting 🙂

Hi, But in the case i want to sum the sum item_name column

 

total = bill1+bill2+bill3...bill10

 

 

 

Thank you so much @Anonymous. I had been using a column rather than measure for DISTINCTCOUNT.

Aprreciate you taking the time and resolving my issue.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors