Forum Discussion
Multi-Year cluster column chart structure question
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:
Correct if you change the LABID by the school you should get the same result.
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
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.
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.
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.
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.
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.
