Average Function Affected by Hierarchy

MattRobinson
MattRobinson
in Modeling

Hi, 

 

I have a module which calculates an average using this formula:

 

'DATA01'.'Hours'[AVERAGE: 'DATA01'.Job Code, AVERAGE: 'DATA01'.Date]

 

I want to add a regional context selector to this module so that I can view the average separately for each region. I added Region as a dimension in this module and updated the formula to include the region:

 

'DATA01'.'Hours'[AVERAGE: 'DATA01'.Job Code, AVERAGE: 'DATA01'.Region, AVERAGE: 'DATA01'.Date]

 

After adding region to the formula, I set the Region context selector to "Global" which includes all regions. I then compared this result to the result of my first formula above and expected the calculations to match perfectly. Since the context selector is set to Global, I expect the results to be the same.

 

In contrast to me expectation, about 50% of the values are exactly the same and about 50% of the values are different.

 

Why is this happening?

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @MattRobinson 

     

    So, let me ask you a question, do you have a composite list with all of the Regions rolling up to Global?  I wouldn't think Job Code would be in there, but it certainly could with Job Codes rolling up to Regions which then rolls up to Global?

     

    IF you do have this, change the summary of of your formula from Summary to Formula and that should straighten you out.

     

    Also, I see that Date is a line item that you are doing an average on.  Is this date part of the code in your transactional list?  If so, it would be best to have that in a module dimensionalized by Time, even at the day level UNLESS this makes it crazy sparse.  And by  crazy sparse, I mean that a transaction only has one date associated with it.  For more information on this, please check out this article:  https://community.anaplan.com/t5/Best-Practices/Data-Hubs-Purpose-and-Peak-Performance/ta-p/48866

     

    Basically, this article tells why have Time as part of the code is not a good thing.

     

    Hope this helps,

     

    Rob

  • MattRobinson
    MattRobinson

    Hi Rob,

     

    I have a hierarchy with Regions rolling up to Global.

     

    Job code is not part of that hierarchy, it is just a dimension of the module I am building. Basically, the rows are job codes.

     

    Time is coming from a "DATE"  column in the source data. Currently, I have IDs as the rows and various columns, including Date.

     

    Best,

    Matt

  • rob_marshall
    rob_marshall

    @MattRobinson 

     

    Ok, good deal.  So for your original question about regions rolling up to Global for the average, change the summary to Formula, that should help.

     

    As for the the second part, and again, i don't know the use case, but why are you bringing in transactional (invoice level) data?  Can that be summarized up upon load into Anaplan?  For instance, if you had the following columns in the source file: invoice_nbr, invoice_date, Region, Job Code you could bring this into a module which is dimensionalized by Job Code, Regions, and Time and everything will already be summarized for you.

     

    Just a thought and I know I am talking about your use case without knowing all the facts.

     

    Rob

Categories