Former Member

### How to calculate weighted average of sales share in WEBI?

In the above picture - I have a store, which sells 4 brands (ABC, BCD, CDE and DEF) and certain categories. For each store-brand-category I have some sales amount. I need to calculate the final share of sales in Webi - which is the weighted average sales share for each store-brand-category. The column Totals within brand gives us the total sales within each brand for all categories sold in that store. But the only key Figure I have is Sales. Share of Sales and Totals within Brand is calculated in the report.

If I aggregate it in Webi - totals within brand would also give me the same number as Sum of Sales - which is 10088.

Could you help me? if this cant be done in Webi - can it be done in BEx? Or the only option left is to store the totals in an infoprovider?

10|10000 characters needed characters exceeded

• Former Member
Aug 30, 2017 at 10:30 AM

It's not quite clear what you're asking for. Could you upload an image of what you'd like the totals to be?

10|10000 characters needed characters exceeded
• Former Member

Hi Charles,

In the grand calculation - yes i would have more store. But here - 20.11 is just the average sales share of a category within the brand for this store 123. If I apply the formula you have provided it would yeild 100% at the total level (for all brands and categories within store 123).

• Aug 29, 2017 at 02:06 PM

what is the formula of shares of sales?

Totals within brand you can claculate like=sum(sales) in (Brand)

10|10000 characters needed characters exceeded
• Former Member

We can get the total for each Brand (for all categories) in various ways - I normally use

Total sales = Sales foreach(Brand) In Block . But there are several methods and they are all correct.

But When I have the same formula in the totals line - it would give me the total sum - 10088 and not 50154 - which is the addition of individual totals at each line. I need to add all the totals from each individual row to get the weighted average of sales share. Otherwise the sales share for Total block would be 100%.

• Aug 30, 2017 at 02:04 AM

If I understand what you are asking, you should be able to first calculate the "Totals within Brand" in Webi by using ForAll to remove Category from the calculation context, which would be something like:

`=Sum([Sales] ForAll([Category]))`

Then you could take Sales / Total within brand to get the percentage you are looking for

10|10000 characters needed characters exceeded
• Former Member William Ayd

Hi William - my apologies for the confusion.

In the image - the last row contains totals. I need to calculate the totals under "Share of Sales" Column - the figure 20.11. The only measure we get from the BO query is Sales. The other two measures are calculated - Share of Sales and Totals within brand. Share of Sales = Sales/Totals within brand

• Aug 31, 2017 at 06:23 PM

Hi Rahul,

Requirement is not clear but however you can try by applying break based on store or whatever and try with foreach or forall or In operaters.

I'm sure that it will work with this functions else try with running sum or running average functions like that.

Thanks,

Durga