2 Replies Latest reply on Feb 7, 2019 2:22 PM by Patrick Schenkel

# Partial Grouping or Allocating unknown to groups

I have dirty data from a very rigid system.  I have a dimension that is unknown that I need to allocate by historically documented percentages.  Is it possible to create a grouping from a percentage?  For example

CountrySales
USA1000
Mexico500
Russia1200
Denmark800
Unknown200

Allocation based on historic patterns:

Country to allocate toAllocation
Russia50%
USA30%

Desired Output:

CountryNew Sales
USA1000 + (30% * 200)=1060
Mexico500
Russia

1200 + (50%*200)=1300

Denmark800

Any help is appreciated!

Thanks,
Pat

• ###### 1. Re: Partial Grouping or Allocating unknown to groups

Hi Patrick

I would suggest creating an allocation to call 5 countries - you just have 2 that are 0  Mexico and Denmark - If this is a regular thing Suggest you keep it in a table and join that table your primary table then you could do something like

sales to allocate = {fixed : sum(if country="Unknown" then sum(sales)}

{fixed [country]: ( if min( [country]) <> 'Unknown" then sum(Sales)  + sum(sales to allocate) * {Allocation %) )}

I don't have your txwb  workbook so I cant test the formulas - you may need to play with them a bit

and depending on the actual data and the sheet you will probably have to play with the LOD dimensions before the colon

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Partial Grouping or Allocating unknown to groups

Thanks Jim--

Very helpful--Also got an assist from Josh Weybourne and Joost Rietdijk.  This is how the problem was solved.

Calc 1:

If Country = 'Unknown' then sales else 0 end

Calc 2:

{Fixed Country : Sum (Calc1)}

Calc 3:

if attr(country) = 'USA' then sum(sales) + sum(calc 2) * .3
elseif attr(country) = 'Russia' then sum(sales) + sum(calc 2) * .5
elseif attr(country) = 'Canada' then sum(sales) + sum(calc 2) * .2
else     sum(sales) end

Thanks,
Pat