Split query into 1 table with 4 areas

[Anonymous]

I have a query which in the area it has values of 

project           Area

1                     ncsa

2                    mena

3                    apac

4                    earc

5                    ncsa

I would like to split each row based on the area into a table that counts the number of projects based on the area. for example,

table 1

# of projects in area                  area

5                                                mena

3                                                apac

how can I split my query or use DAX to create a new table

[AlB · Accepted solution]

Hi @Anonymous 

The example is a bit confusing. I'm not sure I understand correctly. Create a new calculated table:

NewTable =
ADDCOLUMNS (
    DISTINCT ( Table1[Area] ),
    "NumProjects", CALCULATE ( COUNT ( Table1[project] ) )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[jdbuchanan71]

You can get the count of uniqe projects using this measure.

# of projects in area = DISTINCTCOUNT ( YourTable[project] )

Then you would add the area and that measure into a table visual to get the project count by area.