Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello experts,
I have a table with Bill # and item name as follows:
The 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:
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!
Solved! Go to Solution.
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 @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
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |