Skip to Content
author's profile photo Stefan Riegel
0
Stefan Riegel

Summarize Dimensions in table by percentage of measure

Posted on Jul 16, 2020 at 08:07 AM 72 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.

webi-group-by-percent.png (42.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Stefan Riegel
    Stefan Riegel
    Posted on Jul 23, 2020 at 05:55 AM
    0

    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.


    1-percent.png (208.6 kB)
    country-other-new.png (213.2 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Venkateswara Y Guptha
    Venkateswara Y Guptha
    Posted on Jul 20, 2020 at 07:51 AM
    0

    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.


    percentagecalculation.png (203.4 kB)
    formulatodecidecountrysummary.png (189.2 kB)
    dimensionsummarybasedonpercentageofsales.png (206.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Stefan Riegel
    Stefan Riegel
    Posted on Jul 21, 2020 at 07:11 AM
    0

    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:


    basedata.png (23.0 kB)
    percentage-variable.png (86.0 kB)
    country-other-variable.png (87.5 kB)
    tables.png (55.8 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Venkateswara Y Guptha
    Venkateswara Y Guptha
    Posted on Jul 29, 2020 at 04:45 PM
    0

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.