your cart

How to calculate hierarchy target by using LOD calculation

      In business, some company will set target to low level, then the upper level target is summed up by lower level. Such as dealer -- store -- rep. When we calculate for rep target, no issue. But when we need calculate for dealer or store target, we will have trouble. Let's take an example with Sample - Superstore data.
    We will use state -- category -- sub-category hierarchy. First, we create a simulation target for sub-category. To make it simple, I only use "California" and "2017". Formula as below:
IF [State]='California' AND YEAR([Order Date])=2017 THEN
CASE [Category] 
WHEN 'Furniture' THEN
CASE [Sub-Category]
WHEN "Bookcases" THEN 11392
WHEN "Chairs" THEN 12311
WHEN "Furnishings" THEN 4093
WHEN "Tables" THEN 13625
END
WHEN 'Office Supplies' THEN
CASE [Sub-Category]
WHEN "Appliances" THEN 12570
WHEN "Art" THEN 2346
WHEN "Binders" THEN 12653
WHEN "Envelopes" THEN 558
WHEN "Fasteners" THEN 155
WHEN "Labels" THEN 650
WHEN "Paper" THEN 6781
WHEN "Storage" THEN 13740
WHEN "Supplies" THEN 424
END
WHEN 'Technology' THEN
CASE [Sub-Category]
WHEN "Accessories" THEN 14890
WHEN "Copiers" THEN 14592
WHEN "Machines" THEN 7852
WHEN "Phones" THEN 14306
END
END
END
      Then make default aggregation to "Average" because each row will have a target value so that we only take average.

    Then we state, category and sub-category to row, measure name to column and choose sales and target, measure value to text as below:

    If we remove sub-category, then you will see target become wrong because it average each target value of all rows.

    To fix this, we need create a target for category by using LOD as below:
    {FIXED [Sub-Category]:AVG([Target])}
    Then darg it to chart, you will see below:



Blog Category