Forum Discussion

shawn3474's avatar
shawn3474
Frequent Visitor
8 years ago
Solved

Having trouble recreating a visualization as a table.

 

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"))

 

TeamNamePointsofAvailabilitySmallMediumLargeXLarge
AppTech17935198
BackUp21835198
Cherwell0 35198
Cloud and Automation Architects241035198
Corptax SaaS291535198
DataCenter22935198
DBA512435198
DBA - TBS10635198
Desktop Services451835198
Monitoring462135198
Netnames SaaS4235198
Network783635198
PMO481935198
Service Desk10535198
SharePoint7235198
Storage321535198
TAS12535198
Telecom281435198
UE251235198
Unix642735198
Vendor/Contractor5235198
Web582635198
Windows863835198
Need Help
  • MarkS's avatar
    MarkS
    8 years ago

    Hi shawn3474,

    Could you try

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

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      shawn3474
      Frequent Visitor
      8 years ago

      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.

  • MarkS's avatar
    MarkS
    Resolver IV
    8 years ago

    Hi shawn3474,

    Could you try

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

     

     

    • shawn3474's avatar
      shawn3474
      Frequent Visitor
      8 years ago

      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.