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
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
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.