Forum Discussion
Unique Count and grouping
Hello All,
I have a below table.
| Compnay Name | Spend | Fiscal Year | Department |
| Water | 5000 | 2022 | Sea |
| Rock | 10000 | 2022 | Forest |
| Salt | 3000 | 2021 | Sea |
| Rock | 1000 | 2022 | Forest |
| Water | 6000 | 2021 | Sea |
| Salt | 20000 | 2022 | Sea |
| Water | 50000 | 2021 | Sea |
| Water | 50000 | 2022 | Sea |
What I am looking for output table is below:
| Department | Unique Company Count | Company Count with more than >3 Entries | Company Count with more than =2 Entries |
| Sea | 2 | 1 | 2 |
| Forest | 1 | 0 | 1 |
| Grouping Of company as per top spend as per Dept | Total Spend |
| 1--2 | |
| 2--4 |
Will this be possible to do it on power bi. Any help will be appericated.
cyborgandy , Measures
Unique Company Count = DistinctCount(Table[Compnay Name])
Count with more than >3 Entries = countx(filter(values(Table[Compnay Name]), calculate(count(Table[Compnay Name]) ) >3) , Table[Compnay Name])
Count with more than >=2 Entries = countx(filter(values(Table[Compnay Name]), calculate(count(Table[Compnay Name]) ) >=2) , Table[Compnay Name])
1 Reply
cyborgandy , Measures
Unique Company Count = DistinctCount(Table[Compnay Name])
Count with more than >3 Entries = countx(filter(values(Table[Compnay Name]), calculate(count(Table[Compnay Name]) ) >3) , Table[Compnay Name])
Count with more than >=2 Entries = countx(filter(values(Table[Compnay Name]), calculate(count(Table[Compnay Name]) ) >=2) , Table[Compnay Name])