Forum Discussion

RyanLMoran's avatar
RyanLMoran
Helper I
5 years ago
Solved

Multi-Year cluster column chart structure question

Hello:         I'm looking to implement a clustered column chart that will show two pieces of data per year.  I want each year to show the number of labs that were in extistence and the total numbe...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi RyanLMoran ,

     

    I assume that you have a LABID so you need to add the following two measures:

     

    NumLabs =
COUNTROWS (
    FILTER (
        ALL ( Labs[Install date], Labs[ID] ),
        Labs[Install date] <= MAX ( Labs[Install date] )
    )
)


TotlSize =
SUMX (
    FILTER (
        ALL ( Labs[Install date], Labs[ID], Labs[Lab size] ),
        Labs[Install date] <= MAX ( Labs[Install date] )
    ),
    Labs[Lab size]
)

     

    final result below and in attach PBIX file:

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi RyanLMoran ,

 

I assume that you have a LABID so you need to add the following two measures:

 

NumLabs =
COUNTROWS (
    FILTER (
        ALL ( Labs[Install date], Labs[ID] ),
        Labs[Install date] <= MAX ( Labs[Install date] )
    )
)


TotlSize =
SUMX (
    FILTER (
        ALL ( Labs[Install date], Labs[ID], Labs[Lab size] ),
        Labs[Install date] <= MAX ( Labs[Install date] )
    ),
    Labs[Lab size]
)

 

final result below and in attach PBIX file:

 

  • RyanLMoran's avatar
    RyanLMoran
    Helper I
    5 years ago

    Hi Miguel,

     

        I'll be honest, I'm not sure what you're referring to when you mention LABID, but looking at your results it's EXACTLY what I"m trying to get for the column chart.  Thanks for pointing me in the right direction on this

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      You show one line per lab, do those lines have unique identifiers. I say LABID but can be a name or something else.

       

      That will allow to make the count of the rows. 

      • RyanLMoran's avatar
        RyanLMoran
        Helper I
        5 years ago

        Hi Miguel,

         

            This is essentially how I have the excel sheet structured right now, as shown in the snippet.  It's the School name, the lab size (meaning number of PC's) the install date (the year) and the city, State and address.  So, I'm assuming that I would replace ID with School to get the results and populate the column chart correctly?