Total not working at bottom of straight table

[#1]

Hi,

I am hoping someone can help me. I have the formula below which gives me the correct figures that I need but the Total line at the bottom of the straight table is just showing a dash. I have tired moving the location of the Sum to the start of the formula which then shows the total but only using one if statement.

IF Statement Expression

If(Performance_Category = 'Red8', Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}>} ActualIncidents),

If(Performance_Category = 'Red19', Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}>} ActualIncidents),

If(Performance_Category = 'Amber20', Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}>} ActualIncidents),

If(Performance_Category = 'Green60', Sum ({<year={$(=max(year))}>} ActualIncidents),

If(Performance_Category = 'Urgent', Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}>} ActualIncidents),

If(Performance_Category = 'Routine', Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}>} ActualIncidents)))))))

This is the other one I tried but could only make it work on the one if statement.

Sum({<year={$(=max(year))}>}(If(Performance_Category = 'Red8', ActualIncidents)))

Would appreciate any help people can give me

Thanks

[danielrozental · Accepted solution]

to make your expression work as it is you would need to do something like

sum(aggr(

<your expression here>

, Performance_Category))

I would also suggest changing it to something like

Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}, Performance_Category={'Red8','Red19','Amber20','Urgent','Routine'}>} ActualIncidents)

+ Sum ({<year={$(=max(year))},Performance_Category={'Green60'}>} ActualIncidents)

[#3]

Thank you so much I used the solution you gave at the bottom of your reply and it give me to correct answer when I calculated the figures together.

Thank you again

Sum ({<year={$(=max(year))},TeleAdviceFlag=-{1}, Performance_Category={'Red8','Red19','Amber20','Urgent','Routine'}>} ActualIncidents)

+ Sum ({<year={$(=max(year))},Performance_Category={'Green60'}>} ActualIncidents)