Multi-Year cluster column chart structure question

[RyanLMoran]

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.

[MFelix · Accepted solution]

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]

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]

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]

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?

[MFelix]

Correct if you change the LABID by the school you should get the same result. 

[RyanLMoran]

Hi Miguel,

      Super helpful and I'm grateful! I've got a couple of quick questions that I'm stuck on.  For some reason it seems to be computing the number of PC's and labs incorrectly.  Can you see what might be off?  For the labs, it seems to be taking the name field as a value.  I only have 18 labs but it's computing 3 for the first year, where there should only be 2, and 19 for the final year where there should only be 18 total labs.

   For the number of PC's, the first year, 2016, should only be 26, but it's giving me a value of 220, then the final year for the total number of PC's that we have in all of our labs it is computing 388 which is double the total number of 194 PC's that we currently have.  I

   I've looked through it and I'm not sure why it's adding 1 total lab to each year and why it's giving an incorrect PC count.  Below are the two measures that I have for PC's and Labs.  Followed by screenshots of my Power BI Data structure, Column results and Excel structure.  Can you help?  Thank you

[MFelix]

Hi @RyanLMoran ,

Are there for any chance any rows repeat on your data? Is there any school that is repeated? 

I see that you have a line per each of the schoold and the years but if for any chance you have repeated values the calculations can be miss counted.

Looking at the screen you present I'm not abble to check if there is repeated values on the schools names but the calculations should work properly.

Try to go to the query editor and add an Index column and then use that index for the calculation instead of the school name and chek if the additional values still appear in the final value.

[RyanLMoran]

Hi Miguel,

      Thanks for all of the help.  You telling me to go back and look at my data source pointed me to how I had it badly set up.  When I was initially trying to figure out how to get the sum I put an equation into the Excel worksheet to sum the lab size totals.  That sum was a row at the bottom of the lab size.  So, your measure worked as it was grabbing my sum and adding that to the total.  When I removed that and cleaned up my data source the number of labs and total PC's are summing and aggregating correctly.  I added your measures to my notes to study and learn from.  I truly appreciate all of the help and guidance.  Thank you.

[Ashish_Mathur]

Hi,

Try this solution.  To your visual, drag Year to the X-axis.  Write these measures:

Count = countrows(Data)

Number of PC = sum(Data[lab size])

Hope this helps.

[RyanLMoran]

Hi Ashish,

      That's great! Straightforward and to the point.  If I wanted each year to show the running total of all labs and PC's to date, rather than just the total for that year, would I just structure the sum and count measures a bit differently? Would it give me the totals for the current year plus the previous year(s) doing a Sumx or countx?  I like the option of showing just the totals for the current year, as these measure do, but also having the option to show the totals for all previous years plus the current year, giving a running total.  Thanks, I appreciate the guidance while I'm learning this.

[Ashish_Mathur]

Thank you.  For getting a running total, we must have a proper Date column.  If you do not have one but have Year and Month as seperate columns, then we can always create a date column.  If you have only a Year column, then we can create a Date column assuming the Day and Month to be 1 respectively.  So what's your case?  Also, share the link from where i can download your PBI file.