Having trouble recreating a visualization as a table.

[shawn3474]

I am basically trying to recreate this visulation as a new table so I can work with the data:

Here is the problem I am having.   As you can see below, Small shows the number of total projects by team.  I am trying to pull that out to only show small projects for that team.  Below are the formulas I am using.  As you can see from the data above AppTech small should be 3 not 9 so while the formula does split things out by team it is when I try to add the filter that I run into problems.

Formula for column small:  Small = CALCULATE(DISTINCTCOUNT(Table1[Project_Name]))

Formula for Medium, Large, and XLarge as appropriate:  Medium = CALCULATE(DISTINCTCOUNT(Table1[Project_Name]),FILTER(Table1, [ProjectSizeValue] = "Med"))

TeamName	PointsofAvailability	Small	Medium	Large	XLarge
AppTech	17	9	35	19	8
BackUp	21	8	35	19	8
Cherwell	0		35	19	8
Cloud and Automation Architects	24	10	35	19	8
Corptax SaaS	29	15	35	19	8
DataCenter	22	9	35	19	8
DBA	51	24	35	19	8
DBA - TBS	10	6	35	19	8
Desktop Services	45	18	35	19	8
Monitoring	46	21	35	19	8
Netnames SaaS	4	2	35	19	8
Network	78	36	35	19	8
PMO	48	19	35	19	8
Service Desk	10	5	35	19	8
SharePoint	7	2	35	19	8
Storage	32	15	35	19	8
TAS	12	5	35	19	8
Telecom	28	14	35	19	8
UE	25	12	35	19	8
Unix	64	27	35	19	8
Vendor/Contractor	5	2	35	19	8
Web	58	26	35	19	8
Windows	86	38	35	19	8

[MarkS · Accepted solution]

Hi @shawn3474,

Could you try

 Medium = CALCULATE(DISTINCTCOUNT(Table1[Project_Name]),[ProjectSizeValue] = "MED")

[shawn3474]

Mark...This worked.!!!!   Thank you very much for the help!  This created a circular dependency which I fixed by adjusting the table relationships.  I am good to go now.

[v-yuezhe-msft]

@shawn3474,

If your table is as below, select ProjectSizeValue column, then click Pivot column in Query Editor to get your desired result.




Regards,
Lydia

[shawn3474]

This is a new table I created from another table.  The data is not stored that way so a pivot on this won't work but thank you for the suggestion.