# 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: