Forum Discussion
Distinct count summarisation issues
Hi all,
I am trying to do a count distinct of ID within my data set but i am struggling. Unfortuantely I am unable to share my data but i will try and explain below.
I am using a tabular model with relationships built between tables. When looking at ID by division and product i get a result similar to the below. Division and product are located in one table and ID is located in another table. There is a relationship between these tables as otherwise I wouldn't be able to get the data below 'linked'
| Division | Product | ID |
| A | 1 | A001 |
| B | 3 | A002 |
| C | 6 | A003 |
| B | 3 | A004 |
| B | 3 | A005 |
| B | 4 | A006 |
| C | 7 | A007 |
| B | 5 | A008 |
| C | 6 | A009 |
| A | 2 | A010 |
| A | 1 | A001 |
| B | 3 | A002 |
I then want to do a distinct count of the IDs, bringing back the total as 10 (the last two records in the above are duplicates). This is all fine but when it comes to provide a summary at a higher level the results come out the same for all divisions products. Such as the below
| Division | ID |
| A | 10 |
| B | 10 |
| C | 10 |
| Product | ID |
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
| 7 | 10 |
| Division | Product | ID |
| A | 1 | 10 |
| A | 2 | 10 |
| B | 3 | 10 |
| B | 4 | 10 |
| B | 5 | 10 |
| C | 6 | 10 |
| C | 7 | 10 |
Whereas what I want is the following
| Division | Product | ID |
| A | 1 | 1 |
| A | 2 | 1 |
| B | 3 | 3 |
| B | 4 | 1 |
| B | 5 | 1 |
| C | 6 | 1 |
| C | 7 | 2 |
Any suggestions?
thanks
Hi,
Please check the below picture and the attached pbix file.
I am not sure if I understood your question correctly, but one of ways is to create a measure like below.
2 Replies
Hi,
Please check the below picture and the attached pbix file.
I am not sure if I understood your question correctly, but one of ways is to create a measure like below.
Hi,
As your sample data I create two tables in Power BI and made a relationship between them:
- Then create a measure on Sheet with Id :
CntDivision = DISTINCTCOUNT(Sheet114[Id])- Then use the particular measure in my visual as below :Appreciate to Kodus
