Total Rows Not Working

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?

The measure is

Anticipated Tuition =

IF(
HASONEVALUE(projections[Projection Category]),
SWITCH(
TRUE(),
VALUES(projections[Projection Category])= “UG Civilian”, ‘Key Measures - Projections’[Projections] * (429 * 3),
VALUES(projections[Projection Category])= “UG Military”, ‘Key Measures - Projections’[Projections] * (250 * 3),
VALUES(projections[Projection Category])= “GR Civilian”, ‘Key Measures - Projections’[Projections] * (714 * 3),
VALUES(projections[Projection Category])= “GR Military”, ‘Key Measures - Projections’[Projections] * (599 * 3)))

the first part of your measure is the answer

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?

Hi @jmwdba,

@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 hope this is helpful.

Thats correct. It should be a total of everything above.

I just want it to return the total of the rows that show in the visual.

@Melissa is correct, you definitely want to read the links so you better understand what is going on.

For now, see if this will provide the response you are looking for, without seeing your model, I’m not 100% that this is your solution:

VAR ugCIV = ‘Key Measures - Projections’[Projections] * (429 * 3)
VAR ugMIL = ‘Key Measures - Projections’[Projections] * (250 * 3)
VAR grCIV = ‘Key Measures - Projections’[Projections] * (714 * 3)
VAR grMIL = ‘Key Measures - Projections’[Projections] * (599 * 3)

IF(
HASONEVALUE(projections[Projection Category]),
SWITCH(
TRUE(),
VALUES(projections[Projection Category])= “UG Civilian”, ugCIV,
VALUES(projections[Projection Category])= “UG Military”, ugMIL,
VALUES(projections[Projection Category])= “GR Civilian”, grCIV,
VALUES(projections[Projection Category])= “GR Military”, grMIL),
ugCIV + ugMIL + grCIV + grMIL)
1 Like

Got it. I guess I was having a day. Below is how I completed out the formula and all is well now.

Anticipated Tuition =
VAR UGCIV = ‘Key Measures - Projections’[Projections] * (429 *3)
VAR UGMIL = ‘Key Measures - Projections’[Projections] * (250 * 3)
VAR GRCIV = ‘Key Measures - Projections’[Projections] * (714 * 3)
VAR GRMIL = ‘Key Measures - Projections’[Projections] * (599 * 3)

RETURN
IF(
HASONEVALUE(projections[Projection Category]),
SWITCH(
TRUE(),
VALUES(projections[Projection Category]) = “UG Civilian”, UGCIV,
VALUES(projections[Projection Category]) = “UG Military”, UGMIL,
VALUES(projections[Projection Category]) = “GR Civilian”, GRCIV,
VALUES(projections[Projection Category]) = “GR Military”, GRMIL),
UGCIV + UGMIL + GRCIV + GRMIL)

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.

I started with this video https://youtu.be/zJSxWOOL98I

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.

Anticipated Tuition =

VAR UGCIV = CALCULATE(‘Projection Measures’[Projections] * (429*3), projections, projections[Projection Type] = “UGRD/Civilian”, VALUES( Dates[Academic Year])

)

VAR UGMIL = CALCULATE(‘Projection Measures’[Projections] * (250*3), projections, projections[Projection Type] = “UGRD/Military”, VALUES( Dates[Academic Year])

)

VAR GRCIV = CALCULATE(‘Projection Measures’[Projections] * (714*3), projections, projections[Projection Type] = “GRAD/Civilian”, VALUES( Dates[Academic Year])

)

VAR GRMIL = CALCULATE(‘Projection Measures’[Projections] * (599*3), projections, projections[Projection Type] = “GRAD/Military”, VALUES( Dates[Academic Year])

)

RETURN

IF(

HASONEVALUE(projections[Projection Category]),

SWITCH(

TRUE(),

VALUES(projections[Projection Category]) = “UG Civilian” && VALUES(Dates[Academic Year])= “2020-21”, UGCIV,

VALUES(projections[Projection Category]) = “UG Military” && VALUES(Dates[Academic Year])= “2020-21”, UGMIL,

VALUES(projections[Projection Category]) = “GR Civilian” && VALUES(Dates[Academic Year])= “2020-21”, GRCIV,

VALUES(projections[Projection Category]) = “GR Military” && VALUES(Dates[Academic Year])= “2020-21”, GRMIL),

UGCIV + UGMIL + GRCIV + GRMIL)

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