Skip to Content
0
Jul 16, 2020 at 08:07 AM

Summarize Dimensions in table by percentage of measure

89 Views Last edit Jul 16, 2020 at 08:19 AM 2 rev

Dear all,

I ran into problems trying to solve the following requirement. Probably sombeody can help point me into the right direction.

Problem:
I have a basic sales report whicht shows in a table turnovers by Region | ArticleGroup | Country.
Now it's required to summarize all countries which are below 1% of the total turnover per ArticleGroup into "Others".

See example here:

Approaches:

I managed to calculate the percentage per country withing the block.

[percentage] = [NetSales]*100 / ([NetSales] In ([Region];[ArticleGroup]))

Then I wanted to create a new variable dimensions that groups Country by that percentage.

[country_other] = IF [percentage] In([Region];[ArticleGroup];[Country]) <= 1 or IsNull([percentage] In([Region];[ArticleGroup];[Country])) THEN "Others" ELSE [Country]

But that didn't work. It works fine as long as the [Country] is still in the table (but then it does not sumarize the countries). But as soon as I remove [Country] dimensions from the table I get #Multivalue errors.

Environment:

I'm working on Business Objects 4.2 SP5 P7 Web Intelligence on BEx Query (BW 7.6 P3)

As what I did so far did not work at all, I'm totally open for any suggestions.

Thanks in advance.

Attachments