Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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
@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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |