Forum Discussion
Distinct count excluding blank rows in a matrix
Chart is like below
| Contract Type | Nos | Designation | Location |
| Permanent | 1 | Manager 1 | London |
| Permanent | 1 | Manager 1 | Paris |
| Permanent | 1 | Manager 1 | New York |
| Permanent | 2 | Manager 1 | Brussels |
| Permanent | 2 | Manager 1 | Amsterdam |
| Permanent | 3 | Worker 1 | Washington DC |
| Permanent | 3 | Worker 1 | London |
| Permanent | 3 | Worker 1 | Paris |
| Permanent | 3 | Worker 1 | Brussels |
| Contract | Worker 2 | Amsterdam | |
| Contract | Worker 3 | New York | |
| Contract | Worker 4 | London | |
| Contract | Worker 5 | New York | |
| Contract | Worker 5 | Washington DC |
First Formula Required: Count of Nos
Distinct Count of No excluding Blanks
for above chart, outcome should be 3 (unique count of 1, 2 & 3)
Second Formula Required: Count of Designation
Distint Count of Designation
for above chart, outcome should be 6
At the end, if I can get a table like below, it would be of great help:
| Contract Type | Count of Nos | Count of Designation |
| Permanent | 3 | 2 |
| Contract | 4 | 4 |
| Total | 7 | 6 |
3 Replies
- amitchandak
Super User
menik_sun , You can try measure like
three examples
calculate(distinctcount(Table[Nos]), filter(Table, not(isblank([Nos]))))
calculate(distinctcount(Table[Designation]), filter(Table, not(isblank([Nos]))))
calculate(distinctcount(Table[Designation]), filter(Table, not(isblank([Designation]))))
Thanks for your reply amitchandak . It's showing correct total but not while using field dimension. It is showing total result even after using field as rows.
Below is what I get after using the formula:
Thanks for your reply @amitchandak . It's showing correct total but not while using field dimension. It is showing total result even after using field as rows.
Below is what I get after using the formula:
With suggested formula Formula required for
Contract Type Count of Nos Count of Nos
Permanent 7 3
Contract 7 4
Total 7 7
Hi menik_sun ,
Please try the following measures:
first formula - COUNTROWS(SUMMARIZE(Table, Table[ContractType], Table[Desgination], Table[Nos]))
second formula - COUNTROWS(SUMMARIZE(Table, Table[ContractType], Table[Designation]))
I arrived at the following matrix using ContractType in Rows.
Cheers
