9 Replies Latest reply on Aug 22, 2018 5:02 AM by Okechukwu Ossai

# Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Hello,

I'm trying to attribute a higher aggregated number to a lower detail level.

I have sales per day, and budgets per month. I want to evenly divide the budget per day based on the proportion of sales in that month.

I can get away with it on a month level, but I don't get the right budget contribution as a grand total.

This is because, to avoid duplication, I use the formula min([budget])*[Sales contribution %]. The grand total should be 700 instead of 200.

Is there a way I can get the total sum correctly?

I need to be able to calculate the sales minus the budget, which is now giving an incorrect result on total level as well.

Many thanks!

Kind regards

• ###### 1. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Hi Lotte,

Is it the value you are expecting

Go Analysis --> Totals --> Total All Using --> Sum

Hope this helps kindly mark this answer as correct/helpful so that it will help others

BR,

NB

• ###### 2. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Hi Naveen,

Yes, this is what I'm expecting. However, I've tried that, and when I take out the months from the table, it still doesn't add up correctly, because then he refers back to the formula using min(budget).

So I'm searching for something that corrects my problem on the total aggregated level, not just on the total sum level.

Kr

• ###### 3. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Hi Lotte,

Change your [Budget contribution] formula to;

{FIXED [YEARMONTH]: min([budget])*[Sales contribution %]}

Also change [Budget-Sales difference] to;

SUM([Sales])-SUM(([Budget contribution]))

Hope this helps.

Ossai

• ###### 4. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Thanks! However, it gives me the error 'Level of detail expressions cannot contain table calculations or the ATTR function'...

• ###### 5. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Where is the ATTR coming from? I don't have such errors. Look at my response again. There are 2 modifications. Copy them and replace the existing formulas. It should work except you made changes to your workbook after you posted the sample dataset here.

• ###### 6. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

Hi Lotte,

Check this even after removing year  month field i am getting the value

Okechukwu Ossai  Answer absolutely correct and its giving the perfect value

Calculations:

Attached the workbook for the same

Hope this helps.

BR,

NB

• ###### 7. Re: Attributing a higher aggregation to a lower detail level (Attributing a number on month-level to day-level)

It works - my apologies, I must have done something wrong the first time!

I made one adaptation, and that is that I switched YEARMONTH in the Fixed for DATE, because it allows for a more granular view as well.