How to calculate weighted average of sales share in WEBI?

[Former Member]

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?

[former_member309630]

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

[Former Member]

Hi Durga - 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

[Former Member]

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

[Former Member]

Hey Charles,

Thanks for the answer. I have uploaded an image above. In the image - the last row contains totals. I need to calculate the totals under "Share of Sales" Column - the figure 20.11.

Thanks

[Former Member]

Hi Rahul

I'm assuming that you have more than one store and the figure of 20.11 means that Store 123's total sales represent 20.11% of the overall total for all stores, right?

If so sounds like you need something like:

=Sum([Sales])/Sum([Sales]) In([Stores])

Or

=Percentage([Sales])

[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).

[former_member492038]

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

[Former Member]

Yes - the totals i can get. There are various ways to do it - and all of them yeild the correct result :). 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%.

[former_member492038]

Hmm I'm having a hard time seeing the big picture here. It sounds like there is one measure called "Weighted Average of Sales Share" that you are trying to get to but which is not depicted in your screenshot. Can you provide that so we can see ultimately the number you are trying to get to?

[Former Member]

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

[amitrathi239]

what is the formula of shares of sales?

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

[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%.