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 ,

 

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's avatar
RyanLMoran
Helper I
5 years ago

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.