Summarize Dimensions in table by percentage of measure

[former_member598675]

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.

[former_member598675 · Accepted solution]

Hey All,

I managed to solve the problem.

Solution was, to simply do not calculate the percentage for each country, but calculate the value of 1% from total netsales per Article Group. Then base the IF-clause for the country/others grouping on that value.

It seems to be a problem as long as the measure used in the IF-clause is only calculated and not coming "physcically" on country level. Switching to net sales, which comes from data base on country level seems to do the trick.

[VenkateswaraGY]

Not sure, where is the confusion, or what I am missing, the formulas originally shared by you i.e.

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

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

still worked for me, even with the latest data sample shared by you.

However happy hear that you are able to achieve what you are expecting.

Regards, Venkat.

[former_member598675]

Hello Venkateswara,

thanks a lot for you help - really appreaciate it.

I just tried the very same, based on a simple Excel Data sheet.

But again, as soon as I eliminate the country from the table, I get the #MULTIVALUE instead of the grouped country dimension.
I assume this happens because, as soon as I eliminate country level from the table the percentag is not calculated on country level any more and therefore get's multiple items within _country_other dimension.
I tried to solve this within percentage calculation, using IN operator - but no sucess so far.

How did you make that work within the report table?

Data Source:

Variable _percentage:

=[NetSales] In ([KI Region];[Article Group];[Country])*100/(Sum([NetSales] In ([KI Region];[Article Group])))

Variable _country_other:

=If [_percentage] In ([KI Region];[Article Group];[Country]) <= 1 
     Or IsNull([_percentage] In ([KI Region];[Article Group];[Country]))
Then "Others"
Else [Country]

Report:

[VenkateswaraGY]

Hi Stefan,

Ideally the formulas for the variables you mentioned above are sufficient for what you want to achieve, as we know #MULTIVALUE will whenever any problem in aggregation, you need to cross check your data and need to see if it needs any more dimensions to be included in the context to avoid the ambiguity in calculating aggregation.

I was able successfully achieve the expected behavior with the above shared sample data with the BI 4.2 SP08.

percentagecalculation.png

formulatodecidecountrysummary.png

dimensionsummarybasedonpercentageofsales.png

Hope this helps.

Regards, Venkat.