Forum Discussion
Solved
Count Rows in a column based on another column's distinct value
I want to total the Buyers in column B based on the PO in column A. Example 1 PO = 3 buyers (Total column) I have tried a couple of measures Distinctcount and Count but can't seem to get it.
| PO | Buyer | Total |
| 1001 | B | 3 |
| 1001 | C | |
| 1001 | D | |
| 1002 | A | 4 |
| 1002 | B | |
| 1002 | C | |
| 1002 | D | |
| 1003 | B | 1 |
| 1004 | A | 1 |
| 1005 | A | 2 |
| 1005 | B | |
| 1007 | A | 3 |
| 1007 | B | |
| 1007 | C | |
| 1008 | B | 1 |
| 1009 | B | 2 |
| 1009 | D | |
| 1010 | A | 3 |
| 1010 | B | |
| 1010 | C | |
| 1011 | B | 2 |
| 1011 | D | |
| 1012 | B | 1 |
| 1013 | A | 3 |
| 1013 | B | |
| 1013 | C | |
| 1020 | A | 3 |
| 1020 | B | |
| 1020 | C | |
| 1022 | A | 3 |
| 1022 | B | |
| 1022 | C |
A measure like this should work:
Buyer Count per PO = CALCULATE( DISTINCTCOUNT( [Buyer] ) , ALLSELECTED( {Buyer] ) )The reason for writing the measure this way is in your above example, you want the total number of buyers per PO within a table containing both PO and Buyer, so you need to remove the row context of Buyer from the calculation. This is what the ALLSELECTED here is doing.
2 Replies
A measure like this should work:
Buyer Count per PO = CALCULATE( DISTINCTCOUNT( [Buyer] ) , ALLSELECTED( {Buyer] ) )The reason for writing the measure this way is in your above example, you want the total number of buyers per PO within a table containing both PO and Buyer, so you need to remove the row context of Buyer from the calculation. This is what the ALLSELECTED here is doing.Thank you very much. That works perfectly.