Total Sales by Group (Product Share over specific total)

[kira_whopper]

Hi there,

I have a straight table where I need to calculate each Product's Share over it's Relevan Market.

A Product can be a part of one or more Relevant Markets, or Groups.

It's share is the sales / total sales of the groups it participates.

For example:

Product A sold $50 for Group1 and $100 for Group2.

Group1 total is $1,000 and Group2 total is $500

Product A Share is:

$150 / $1,500

I've tried to use Sum (TOTAL <Group> Sales), Aggr(Sum(Sales), Group), Aggr(NODISTINCT), etc.

Till now, no go.

Attached is a sample file with the problem.

Thanks in advance.

[arulsettu]

this one?

[kira_whopper]

Nope...
That's just the Product Sales / Group Sales Column...
But Group Sales Column is wrong...
For ProdA, should be Product Sales / Sum of Group1 and Group2 Sales

What I need is the right total for each row.

I added a new straight table with the right result for ProdA

[arjunkrishnan]

Hi Friend,

Try This Below Expression ,

Num(Sum(Sales)/Sum(Total <Product>Sales),'#,##0.0 %')

[sunny_talwar · Accepted solution]

Try this expression to calculate the Group Sales:

Sum(Aggr(Sum(TOTAL <ProdGroup> Sales), Product, ProdGroup))

[kira_whopper]

That's exactly it!

Thanks a lot!