So I am curious as to whether there is something about the measure in the matrix that prevents the total rows from working. It gives the numbers just fine on the rows as it should but when it comes to the totals they are blank. I have checked the settings to ensure the totals are toggled on. Is there a battery way to approach the below formula to make the totals work as expected?
IF( HASONEVALUE( projections[Projection Category]) -
the total line is going to return a ‘FALSE’, and you don’t have anything to indicate what to do if it’s false, your SWITCH formula is only handling the ‘TRUE’ response
What do you want the total line to return? a total of each of the above lines?
@Heather is correct, there is no evaluation context coming from projections[Projection Category] in the Total row. So you’d need to bring that context back using an iterator to calculate the correct result.
Below some resources to help you fix it. But you can also use the Search option in the upper right corner because there’s a lot of content on this subject available within the forum.
I think I may be missing something else and I am checking the video about totals not showing correctly but below is what the formula above gives me.
Notice that for April as an example, the total for undergraduate is showing as $26,109,144 instead of $4,219,722. I am not sure where its actually drawing the totals from. Should I be introducing filter logic into the first half of the formula with each VAR? I don’t use variables a lot to know.
How is the filter logic working with your Projections measure (assuming that it’s a measure here)
Part of the issue here is the use of the subtotals, if you look at how the SWITCH measure is working, if the Projection Category is not one of the defined values (UG Civilian, UG Military, GR Civilian, GR Military) - the measure is instead adding each of those variable amounts together.
so the total doesn’t actually have a value on it’s own, and neither do the subtotals.
I think we’re going to have to see a mockup of your model (a PBIX version) to really help you with this.
Below is ultimately what I did to fix the problem with the totals. I guess my next thought is how to make this timeless because this solves it for the upcoming academic year of 2020-21 (which is July 1, 2020 to June 30, 2021). How will I make this be able to handle future academic years without adding each year to the formula? Over time this formula could become massive if looking at 5 or 10 years from now.
@jmwdba Looking at your table image again, I don’t see the year included in your months. Is the report showing all years? Or is the page filtered to a particular school year?
This upcoming year is the first year for this particular model so it only includes the one year and any future year. I am just thinking ahead and how I ensure this formula is durable when future years are added.
if adding the academic year filter to the report fixed it for now, then I think that you probably need to consider putting the filter onto the page (or in the filter pane for the page), then you can remove it from your measure.
Otherwise, I’m out of suggestions until we can get a look at your model, try to make a simple version of the model that repeats your issue, but doesn’t include sensitive data.
