Sum: exclude dimension null values

[edemerdjieva]

Hello all,

I have a chart that displays total of sales by business unit for a marketing campaign. In the data set there are sales which are not concerned by the marketing campaing. So I need to exclude these sales from the KPI calculation. This is the equivalent of "Delete null value" option of a dimension. However, I don't have the dimension in question in the chart.

Is there a way to do this in Set Analysis?

Here is the expression of the total sales amount (CA Net TTC):

=Sum(Aggr(Sum(TOTAL <Ticket> [CA Net TTC Devise Support]),Ticket,[Code Action marketing]))

Thanks in advance for your advice!

[YoussefBelloum · Accepted solution]

Hi,

just add a set analysis on the last Sum to exclude the values you want, like this:

=Sum( {<Field-={'values to exclude'}>}  Aggr(Sum(TOTAL <Ticket> [CA Net TTC Devise Support]),Ticket,[Code Action marketing]))

[edemerdjieva]

Thanks Youssef!

It worked fine:

{<[Code Action marketing]-=''>}

[YoussefBelloum]

You're welcome

Don't forget to close the thread by marking correct and helpul answers.

Good luck

[edemerdjieva]

Not sure how to do this.

I marked "Correct answer". Is it closed now?

[YoussefBelloum]

Yes, it is in green