Filtering where column total is null/0

rbartley — Sat, 16 Nov 2024 20:37:51 GMT

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?

Re: Filtering where column total is null/0

LReeve — Wed, 19 Jun 2019 14:50:34 GMT

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

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

Re: Filtering where column total is null/0

rbartley — Thu, 20 Jun 2019 08:56:02 GMT

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.

topic Re: Filtering where column total is null/0 in App Development

Filtering where column total is null/0

Re: Filtering where column total is null/0

Re: Filtering where column total is null/0