Distinct count excluding blank rows in a matrix

[menik_sun]

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

[Anonymous]

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

[amitchandak]

@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]))))

[menik_sun]

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