Inflation Calculation at Sub Category Level

Question

I am needing to specify the "level of calculation" for an inflation formula in my dataset, and I have not found a good way to do so yet. See the tables below for a simplified model/calculation. In this model, I need the inflation calculation to compare this year average cost to last year average cost at the sub category level and calculate Inflation $ as (TY avg cost - LY avg cost) * TY Units. I then need the category inflation $ to be a sum of the subcategory Inflation $, rather than a recalculation at the category level.&nbsp;The reason I need the calcuation to work this way is to eliminate the impact of mix changes in my inflation $. i.e. the result should be the $54.10 shown below, not the $25.78 as recalculated at the category level).&nbsp;ItemSub CategoryCategoryTY Units&nbsp;TY Avg Cost&nbsp;LY Units&nbsp;LY Avg Cost&nbsp;&nbsp;&nbsp;11010010&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.505&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00&nbsp;&nbsp;21010020&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11.0015&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.75&nbsp;&nbsp;31010025&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.5030&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.50&nbsp;&nbsp;41010045&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00&nbsp;&nbsp;&nbsp;&nbsp;510100&nbsp;&nbsp;30&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.50&nbsp;&nbsp;62010015&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.0010&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.90&nbsp;&nbsp;72010035&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.5040&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.45&nbsp;&nbsp;82010040&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.7515&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.50&nbsp;&nbsp;92010050&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.00&nbsp;&nbsp;&nbsp;&nbsp;1020100&nbsp;&nbsp;40&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sub CategoryCategoryTY Units&nbsp;TY Avg Cost&nbsp;LY Units&nbsp;LY Avg Cost&nbsp;AUR Change&nbsp;Inflation $&nbsp;&nbsp;10100100&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.1380&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.36&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 35.94&nbsp;20100140&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.41105&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.28&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.13&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18.17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CategoryTY Units&nbsp;TY Avg Cost&nbsp;LY Units&nbsp;LY Avg Cost&nbsp;AUR Change&nbsp;Inflation $&nbsp;&nbsp;Correct:100240&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.63185&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.52&nbsp;&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 54.10&nbsp;Incorrect:100240&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.63185&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.52&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.11&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25.78&nbsp;I am struggling to find a way to accomplish this in a measure. My most recent attempt is below, but this appears to be recalculating at the category level still (i.e. the category Inflation $ are not the sum of the sub-category Inflation $). I have a fact table with units and dollars by date, location, item as well as attribute tables for date and item. TY Avg Cost, LY Avg Cost and TY Units are all measures within my data model. LY columns are calculated using or EDATE(fact_date, -12).&nbsp;Inflation $ =VAR subcat_list =CALCULATETABLE (SUMMARIZE ('Item Attribute','Item Attribute'[Sub Category],"@TY Avg Cost", [TY Avg Cost],"@LY Avg Cost", [LY Avg Cost],"@TY Units", [TY Units],"@inflation_calc",([TY Avg Cost]-[LY Avg Cost])*[TY Units]) )RETURNif(NOT ( or (ISBLANK ( [LY Avg Cost] ), ISBLANK ( [TY Avg Cost] ) )),SUMX ( subcat_list, [@inflation_calc] ),BLANK())&nbsp;Thanks in advance!

littlemojopuppy · Answer

First, create a hierarchy of Category/Subcategory.

Then in the measure to define inflation, use ISINSCOPE() to determine which level of the hierarchy is being calculated. Note: check hierarchies from lowest level first to highest level last.

Can you provide some raw data to work with?

Forum Discussion

Inflation Calculation at Sub Category Level

1 Reply

Related content

Calculate Average per category

calculate max per category.

Calculating cumulative total across categories

Category/Subcategory Calculation

% Cumulative inflation

Item	Sub Category	Category	TY Units	TY Avg Cost	LY Units	LY Avg Cost
1	10	100	10	$ 10.50	5	$ 10.00
2	10	100	20	$ 11.00	15	$ 10.75
3	10	100	25	$ 9.50	30	$ 9.50
4	10	100	45	$ 10.00
5	10	100			30	$ 9.50
6	20	100	15	$ 2.00	10	$ 1.90
7	20	100	35	$ 2.50	40	$ 2.45
8	20	100	40	$ 1.75	15	$ 1.50
9	20	100	50	$ 3.00
10	20	100			40	$ 2.50

	Sub Category	Category	TY Units	TY Avg Cost	LY Units	LY Avg Cost	AUR Change	Inflation $
	10	100	100	$ 10.13	80	$ 9.77	$ 0.36	$ 35.94
	20	100	140	$ 2.41	105	$ 2.28	$ 0.13	$ 18.17


		Category	TY Units	TY Avg Cost	LY Units	LY Avg Cost	AUR Change	Inflation $
	Correct:	100	240	$ 5.63	185	$ 5.52		$ 54.10
	Incorrect:	100	240	$ 5.63	185	$ 5.52	$ 0.11	$ 25.78