Filtering where column total is null/0

[rbartley]

Hi everyone,

I would like to hide the 2007 column in the pivot table below as there is no data for the selected countries (EU and Belgium), it is shown as 0 for both countries as I am using Sum(Value), but the all 2007 records are in fact null.  

I tried to calculate the overall column total by using the expression: 

=Aggr(Sum(If(IsNull(Value),0,Value)),[Indicator Code],[Time Period])

Where the Indicator is represented by the CTX.. value and Time Period represents the year in the pivot section.  However, while this displays the correct result in the row for Belgium, it shows a null (-) in the EU row.  As you can see from the formula above, I have already tried to translate the null to zeros in order to cater for this, but it's not working as expected. 

I wondered whether this was an issue with the pivot table, so I created a straight table, building the expression up step by step, but I still get the same result (nulls in the EU rows).  What I'm expecting to see is 72.44 on both EU and Belgium rows for 2005 and 18 on both rows for 2018 and 0 on both 2007 rows.  

Does anyone have any ideas?

[LReeve]

Does it work by doing something like this for the measure:

=Sum({<Value = {"*"}>} Value)?

[rbartley]

Hi Luke,

Thanks for replying.  Unfortunately, this doesn't sum up all the values at column level, it just shows the same value as Sum(Value).  And when I try to combine it with Aggr(), it reacts the same way as Sum(Value) too, so this does not solve my problem.