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 number of PC's.  So, as shows in my sample of the excel data in 2016 there would be 2 labs with a total of 26 PC's.  So, the chart would show those two bars; 2 and 26, then in 2017 we added 6 labs and 69 PC's so the two bars for the year 2017 would show 8 labs and 95 PC's, etc.. I would still want to have it show a year 2020, although there would not be an increase due to the Covid shutdowns.  Can someone point me in the right direction on how I would start to structure this in PowerBI so that I can get it to show correctly in the Column visual?  Thank you.

 

 

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:

     

11 Replies

  • 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.