cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
palak Frequent Visitor
Frequent 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

Accepted Solutions
JaredK Member
Member

Re: Sum/Count of distinct column values based on another column

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 Smiley Happy

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
5 REPLIES 5
JaredK Member
Member

Re: Sum/Count of distinct column values based on another column

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

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
palak Frequent Visitor
Frequent Visitor

Re: Sum/Count of distinct column values based on another column

Hi @JaredK, 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

JaredK Member
Member

Re: Sum/Count of distinct column values based on another column

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 Smiley Happy

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
palak Frequent Visitor
Frequent Visitor

Re: Sum/Count of distinct column values based on another column

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

Aprreciate you taking the time and resolving my issue.

sixtoquiles Regular Visitor
Regular Visitor

Re: Sum/Count of distinct column values based on another column

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

 

total = bill1+bill2+bill3...bill10