top of page
  • Marek Vavrovic

HuffnerTextile - Multidimensional Model

Updated: Mar 31, 2022


Links to download:






MDX - CALCULATIONS

Gross Profit

Gross Profit Margin

Gross Profit Y-o-Y % Growth

Cost Ratio

Error Ratio

Error Meters Y-o-Y % Growth

Running Meters Last Year

Production Meters Y-o-Y % Growth

Production Revenue 30-Day Moving Average

Warehouse Sales Ratio

Month Ratio

Employee Age

Products % Of Grant Total Sales

% of Grant Total Sales

NAMED SETS

KPI Production Revenue

KPI Production Cost

KPI Product Category Gross Profit Margin

KPI Freight Cost

KPI Revenue Sub Depot

KPI Two-Year Sales Growth


--Gross Profit

WITH MEMBER [MEASURES].[Gross Profit]

AS

[Measures].[Production Revenue] - [Measures].[Production Cost]

SELECT

[Gross Profit] ON 0,

NON EMPTY

[Date Of Manufacture].[Hierarchy].[Year] ON 1

FROM [Huffner Textile Cube];


Gross Profit

2012 $13,469,223.20

2013 $15,799,900.40

2014 $15,418,252.00

2015 $18,147,487.60

2016 $19,717,350.00

2017 $22,168,973.40


 

--Gross Profit Margin

WITH MEMBER [Gross Profit Margin] AS

([Measures].[Production Revenue] - [Measures].[Production Cost])

/ [Measures].[Production Revenue]

, FORMAT_STRING="Percent"

SELECT

[Gross Profit Margin] ON 0,

NON EMPTY

[Date Of Manufacture].[Hierarchy].[Year] ON 1

FROM [Huffner Textile Cube];


Gross Profit Margin

2012 39.05%

2013 38.95%

2014 38.98%

2015 39.07%

2016 39.09%

2017 39.03%


 

--Cost Ratio

WITH MEMBER [Measures].[Cost Ratio]

AS

([Measures].[Production Cost] / [Measures].[Production Revenue]),

FORMAT_STRING="Percent"


SELECT

[Measures].[Cost Ratio] ON 0,

NON EMPTY

[Date Of Manufacture].[Year].[Year] ON 1

FROM [Huffner Textile Cube];


Cost Ratio

2012 60.95%

2013 61.05%

2014 61.02%

2015 60.93%

2016 60.91%

2017 60.97%

 

--Error Ratio

WITH MEMBER [Measures].[Error Ratio]

AS

([Measures].[Defected]/[Measures].[Undefected]),

FORMAT_STRING="Percent"

SELECT

[Measures].[Error Ratio] ON 0,

NON EMPTY

{[Date Of Manufacture].[Year],

[Date Of Manufacture].[Year].[Year]} on 1

FROM

[Huffner Textile Cube];


Error Ratio

All 6.37%

2012 6.54%

2013 6.17%

2014 6.25%

2015 6.40%

2016 6.33%

2017 6.50%

 

--Running Meters Last Year

WITH MEMBER [MEASURES].[Running Meters LY]

AS

SUM(

[Measures].[Running Meters],

PARALLELPERIOD(

[Date Of Manufacture].[Hierarchy].[Year],1,

[Date Of Manufacture].[Hierarchy].CURRENTMEMBER)

)

,FORMAT_STRING="#,#"


SELECT {[MEASURES].[Running Meters LY],[Measures].[Running Meters]} ON 0,

NON EMPTY [Date Of Manufacture].[Hierarchy].[Year] ON 1

FROM

[Huffner Textile Cube];


Running Meters LY Running Meters

2012 (null) 12,508,200

2013 12,508,200 13,885,000

2014 13,885,000 14,434,800

2015 14,434,800 16,492,400

2016 16,492,400 17,648,200

2017 17,648,200 20,627,000

2018 20,627,000 (null)

 

--Production Meters Y-o-Y % Growth

WITH MEMBER [MEASURES].[Production Meters Y-o-Y % Growth]

AS

CASE

WHEN [MEASURES].[Running Meters LY]=null THEN 0

WHEN [MEASURES].[Running Meters]=null THEN 0

ELSE

([Measures].[Running Meters] - [Measures].[Running Meters LY])

/[Measures].[Running Meters LY]

END

,FORMAT_STRING="PERCENT"


SELECT

{[MEASURES].[Running Meters LY],

[Measures].[Running Meters],

[MEASURES].[Production Meters Y-o-Y % Growth]} ON 0,

[Date Of Manufacture].[Hierarchy].[Year] ON 1

FROM

[Huffner Textile Cube];


Running Meters LY Running Meters Production Meters Y-o-Y % Growth

2011 (null) (null) 0.00%

2012 (null) 12,508,200 0.00%

2013 12,508,200 13,885,000 11.01%

2014 13,885,000 14,434,800 3.96%

2015 14,434,800 16,492,400 14.25%

2016 16,492,400 17,648,200 7.01%

2017 17,648,200 20,627,000 16.88%

2018 20,627,000 (null) 0.00%

 

--Gross Profit Y-o-Y % Growth

WITH MEMBER [MEASURES].[Production Revenue_LY]

AS

SUM(

[Measures].[Production Revenue],

PARALLELPERIOD(

[Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CURRENTMEMBER)

)


MEMBER [MEASURES].[Production Cost_LY]

AS

SUM(

[Measures].[Production Cost],

PARALLELPERIOD(

[Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CURRENTMEMBER)

)


MEMBER [MEASURES].[Gross Profit_LY]

AS

([MEASURES].[Production Revenue_LY] - [MEASURES].[Production Cost_LY])

MEMBER [MEASURES].[Gross Profit Y-o-Y % Growth]

AS

DIVIDE(

([Measures].[Gross Profit]-[MEASURES].[Gross Profit_LY] ),

[MEASURES].[Gross Profit_LY] ),FORMAT_STRING="PERCENT"


SELECT {

[Measures].[Gross Profit],

[MEASURES].[Gross Profit Y-o-Y % Growth]

}ON 0,

NON EMPTY

[Date Of Manufacture].[Year].[Year] ON 1

FROM

[Huffner Textile Cube];


Gross Profit Gross Profit Y-o-Y % Growth

2012 $13,469,223.20 (null)

2013 $15,799,900.40 17.30%

2014 $15,418,252.00 -2.42%

2015 $18,147,487.60 17.70%

2016 $19,717,350.00 8.65%

2017 $22,168,973.40 12.43%

 

--Production Cumulative Revenue

WITH MEMBER [MEASURES].[Production Cumulative Revenue]

AS

CASE

WHEN [Measures].[Production Revenue] = NULL

THEN NULL

ELSE

AGGREGATE(

NULL:[Date Of Manufacture].[Hierarchy].currentmember,

[Measures].[Production Revenue])

END

,FORMAT_STRING="CURRENCY"


SELECT {

[Measures].[Production Revenue],

[MEASURES].[Production Cumulative Revenue]

} on 0,

NON EMPTY [Date Of Manufacture].[Hierarchy].[year] on 1

FROM

[Huffner Textile Cube];


Production Revenue Production Cumulative Revenue

2012 $34,488,283.20 $34,488,283.20

2013 $40,564,934.40 $75,053,217.60

2014 $39,552,288.00 $114,605,505.60

2015 $46,451,589.60 $161,057,095.20

2016 $50,445,888.00 $211,502,983.20

2017 $56,794,019.40 $268,297,002.60

 

--Production Revenue 30-Day Moving Average

WITH MEMBER [MEASURES].[Production Revenue 30-Day MA]

AS

AVG([Date Of Manufacture].[Hierarchy].LAG(30):[Date Of Manufacture].[Hierarchy],

[Measures].[Production Revenue])


SELECT [MEASURES].[Production Revenue 30-Day MA] ON 0,

NON EMPTY

[Date Of Manufacture].[Hierarchy].[Date] ON 1

FROM [Huffner Textile Cube]



 

--Error Meters Y-o-Y % Growth

WITH MEMBER [MEASURES].[Error Meters Y-o-Y % Growth]

AS

DIVIDE(([MEASURES].[Defected]-[MEASURES].[Defected_LY]) ,

[MEASURES].[Defected_LY],0)

,FORMAT_STRING="PERCENT"


SELECT {

[MEASURES].[Defected_LY],

[MEASURES].[Defected],

[MEASURES].[Error Meters Y-o-Y % Growth]

} ON 0,

NON EMPTY

[Date Of Manufacture].[Hierarchy].CHILDREN ON 1

FROM

[Huffner Textile Cube];


Defected_LY Defected Error Meters Y-o-Y % Growth

2012 (null) 767,736.00 0.00%

2013 767,736.00 806,567.00 5.06%

2014 806,567.00 849,243.40 5.29%

2015 849,243.40 992,527.90 16.87%

2016 992,527.90 1,050,537.60 5.84%

2017 1,050,537.60 1,258,587.70 19.80%

 

--Warehouse Sales Ratio

WITH MEMBER [MEASURES].[Sub Depot Ship Ratio]

AS

CASE

WHEN [Warehouse].[Warehouse].CurrentMember.Level.Ordinal= 0

THEN 1

Else

[Measures].[Revenue Sub Depot] /

Sum(

[Warehouse].[Warehouse].currentmember.parent,

[Measures].[Revenue Sub Depot]

)

END

,FORMAT_STRING="percent"


SELECT

{

[Measures].[Revenue Sub Depot],

[MEASURES].[Sub Depot Ship Ratio]

} ON 0,

[Warehouse].[WarehouseHierarchy].[Warehouse] ON 1

FROM

[Huffner Textile Cube];


Revenue Ratio

D-KEN $5,536,042.65 1.74%

D-EGT $10,701,656.75 3.36%

D-KAZ $28,638,661.14 8.98%

D-HOK $27,462,689.16 8.61%

D-TRK $40,170,396.00 12.60%

Main-SKA $37,962,941.25 11.90%

D-UK $55,047,481.62 17.26%

D-ITA $42,341,878.78 13.28%

D-CAA $37,000,839.03 11.60%

D-BRA $34,042,704.52 10.67%

 

Month Ratio

Divides monthly values by quarterly to obtain the ratio

WITH MEMBER [MEASURES].[Month to Quarter Ratio]

AS

(PARALLELPERIOD([Ship Date].[Hierarchy].[Month],1,

[Ship Date].[Hierarchy].CURRENTMEMBER),

[Measures].[Revenue Sub Depot])

/


(PARALLELPERIOD([Ship Date].[Hierarchy].[Quarter],1,

[Ship Date].[Hierarchy].CURRENTMEMBER.PARENT),

[Measures].[Revenue Sub Depot])

,FORMAT_STRING="PERCENT"

SELECT {

[MEASURES].[Month to Quarter Ratio],

[Measures].[Revenue Sub Depot]

} ON 0,

NON EMPTY

[Ship Date].[Hierarchy].[Month] ON 1

FROM

[Huffner Textile Cube];



 

--Employee Age

WITH MEMBER [MEASURES].[Employee Age]

as

DATEDIFF("YYYY",

[Employee].[Birth Date].CurrentMember.Member_value,

NOW())


Member [Age Specification]

as

IIF([MEASURES].[Employee Age] > 40,"Too old","Too young")


SELECT {

[MEASURES].[Employee Age],

[Age Specification]

} ON 0,

(

[Employee].[EmployeeHierarchy].[Employee],

[Employee].[Birth Date].[Birth Date]

)ON 1

FROM

[Huffner Textile Cube];



 

--Products % Of Grant Total Sales

(works with Hierarchy)

WITH MEMBER [MEASURES].[Products % GrantTotal Sales]

AS

(

[Product].[ProductHierarchy].CurrentMember,

[Measures].[Total Due Client]

)

/

(

[Product].[ProductHierarchy].[(ALL)].[ALL],

[Measures].[Total Due Client]

)

,FORMAT_STRING="PERCENT"


SELECT

[MEASURES].[Products % GrantTotal Sales] ON 0,

[Product].[ProductHierarchy].MEMBERS ON 1

FROM [Huffner Textile Cube];















 

-- % of Grant Total Sales

(use client hierarchy)

WITH MEMBER [MEASURES].[Client % GrantTotal Sales]

AS

([Client].[ClientHierarchy].CurrentMember,[Measures].[Total Due Client])/

([Client].[ClientHierarchy].[(ALL)].[ALL],[Measures].[Total Due Client])

,FORMAT_STRING="PERCENT"


SELECT

[MEASURES].[Client % GrantTotal Sales] ON 0,

[Client].[ClientHierarchy].[Subregion] on 1

FROM [Huffner Textile Cube];


Client % GrantTotal Sales

Eastern Africa 1.03%

Middle Africa 1.43%

Northern Africa 0.25%

Southern Africa 0.55%

Western Africa 1.70%

Central Asia 5.60%

Eastern Asia 2.93%

South-Eastern Asia 2.15%

Southern Asia 6.53%

Western Asia 8.79%

Australia and New Zealand 3.69%

Eastern Europe 8.58%

Northern Europe 21.24%

Southern Europe 11.38%

Western Europe 2.40%

Northern America 11.17%

South America 10.58%


NAMED SETS


--Top 5 Products by Client Sales

WITH SET [_Top 5 Products By Client Sales]

AS

TOPCOUNT

([Product].[Product Name].[Product Name].MEMBERS,

5,

[Measures].[Total Due Client])


SELECT

[Measures].[Total Due Client] on 0,

[_Top 5 Products By Client Sales] ON 1

FROM

[Huffner Textile Cube];


Total Due Client

FP03437 $773,456.99

FP02865 $572,338.08

FP03569 $569,984.16

FP02599 $530,250.79

FP03039 $507,167.39

 

--Sub Regions Sales by Low Ticket Items

WITH SET [_Low Ticket Items]

AS

[Product].[Price Bucket].&[0]


SELECT

[Measures].[Revenue Sub Depot] ON 0,

(

[Geography].[Subregion].[Subregion],

[_Low Ticket Items]

)ON 1

FROM

[Huffner Textile Cube]


 

--Sub Depot & Category By High Tiket Items

WITH SET [_High Tiket Items]

AS

[Product].[Price Bucket].&[4.]


SELECT

[Product].[Product Category].MEMBERS ON 0,

(

[Warehouse].[Warehouse].CHILDREN,

[_High Tiket Items]

) ON 1

FROM

[Huffner Textile Cube]

WHERE

[Measures].[Revenue Sub Depot];



 

-- Top 10 Client by Sales

WITH SET [_Top 10 Client]

AS

TOPCOUNT

([Client].[Client].[Client].MEMBERS,

10,

[Measures].[Total Due Client])


SELECT

[Measures].[Total Due Client] ON 0,

(

[_Top 10 Client],

[Client].[Country].CHILDREN

)ON 1

FROM

[Huffner Textile Cube]


Total Due Client

Boss Canada $5,956,118.30

Desa Deri Belarus $5,785,367.76

Kufner Uk United Kingdom $5,716,118.37

Gelco United Kingdom $5,637,312.72

Wendler Poland $5,281,600.86

Gollas Turkmenistan $5,181,368.14

Christopo United Kingdom $5,171,972.40

Wl Gore United Kingdom $4,221,548.82

Bytom United Kingdom $4,064,251.86

Pt Sadrafi Moldova $4,015,950.46


 

--This year

WITH SET [THIS YEAR]

AS

StrToMember("[Ship Date].[Hierarchy].[Year].&[" + CSTR(YEAR([Measures].[Now])) + "]")

select

[Measures].[Total Due Client] on 0,

[THIS YEAR] on 1

from [Huffner Textile Cube]


--Last Year

WITH SET [Last Year]

AS

{StrToMember("[Ship Date].[Year].[Year].&[" + CSTR(YEAR([Measures].[Now])) + "]").PrevMember}


SELECT

[Measures].[Total Due Client] on 0,

[Last Year] ON 1

FROM [Huffner Textile Cube]


--Last 30 Day

WITH SET [SHIPPED LAST 30 DAY]

AS


LastPeriods(30,StrToMember("[Ship Date].[Hierarchy].[Date].&[" + CSTR(YEAR([Measures].[Now])

* 10000 + Month([Measures].[Now]) * 100 + Day([Measures].[Now])) + "]"))


SELECT

[Measures].[Total Due Client] ON 0,

[SHIPPED LAST 30 DAY] ON 1

FROM

[Huffner Textile Cube]


--This Month

WITH SET [THIS MONTH]

AS

StrToMember("[Ship Date].[Hierarchy].[Month].&[" + CSTR(YEAR([Measures].[Now]) * 100 + Month([Measures].[Now])) + "]")


SELECT

[Measures].[Total Due Client] ON 0,

[THIS MONTH] ON 1

FROM [Huffner Textile Cube]


--LAST 6 MONTHS

WITH SET [LAST 6 MONTHS]

AS

LastPeriods(6,StrToMember("[Ship Date].[Hierarchy].[Month].&[" + CSTR(YEAR([Measures].[Now])

* 100 + Month([Measures].[Now])) + "]"))

SELECT

[Measures].[Total Due Client] ON 0,

[LAST 6 MONTHS] ON 1

FROM

[Huffner Textile Cube]


 

--Low Error Ratio

WITH SET [_Low Error Ratio]

AS

FILTER

([Product].[Product Subcategory].members,

[Measures].[Error Ratio]<0.059 )


SELECT [Measures].[Error Ratio] ON 0,

(

[_Low Error Ratio]

)ON 1

FROM

[Huffner Textile Cube];


Error Ratio

Cotton 5.80%

Muslin 5.19%

Drill 5.79%

Denim 4.74%

Chiffon 5.63%

Buckram 5.20%

Grosgrain 5.54%

Lace 5.90%


 

--Discounted Months

WITH SET [Discounted Months]

AS

Filter

(

[Ship Date].[Hierarchy].[Month].members,

[Measures].[Discount Pct] <> 0

)

SELECT {

[Measures].[Revenue Sub Depot],

[Measures].[Discount Pct]

}ON 0,

[Discounted Months] ON 1

FROM

[Huffner Textile Cube];


 

--Second Class Customers

WITH SET [_Africa Customers(2.Class Products)]

AS

EXISTS(

[Client].[Client].[Client],

FILTER([Client].[ClientHierarchy].[Continent].&[Africa],

[Measures].[Total Due Client] > 0)

)

SELECT

[Measures].[Total Due Client] ON 0,

(

[Client].[Country].[Country],

[_Africa Customers(2.Class Products)]

)ON 1

FROM

[Huffner Textile Cube];



 

KPI


KPI Production Revenue


WITH MEMBER [MEASURES].[KPI VALUE]

AS

[Measures].[Production Revenue]


MEMBER [MEASURES].[KPI GOAL]

AS

Case

When IsEmpty

(

ParallelPeriod

(

[Date Of Manufacture].[Hierarchy].[Year],

1,

[Date Of Manufacture].[Hierarchy].CurrentMember

)

)

Then [Measures].[Production Revenue]

Else 1.15 *

(

[Measures].[Production Revenue] ,

ParallelPeriod

(

[Date Of Manufacture].[Hierarchy].[Year],

1,

[Date Of Manufacture].[Hierarchy].CurrentMember

)

)END

MEMBER [MEASURES].[KPI STATUS]

AS

Case

When [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] > 1

Then 1

When [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] <= 1

And

[MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] >= .85

Then 0

Else -1

End


MEMBER [MEASURES].[KPI TREND]

AS

Case

When IsEmpty

(ParallelPeriod(

[Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CurrentMember

))

Then 0

When VBA!Abs

((

KpiValue( "Production Revenue" )

-

(KpiValue( "Production Revenue" ),

ParallelPeriod

( [Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CurrentMember)))

/

(KpiValue( "Production Revenue" ),

ParallelPeriod

( [Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CurrentMember)))<=.02

Then 0

When ( KpiValue( "Production Revenue" ) -(KpiValue( "Production Revenue" ),

ParallelPeriod

( [Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CurrentMember)))

/

(KpiValue( "Production Revenue" ),

ParallelPeriod

( [Date Of Manufacture].[Hierarchy].[Year], 1,

[Date Of Manufacture].[Hierarchy].CurrentMember)) > .02

Then 1

Else -1

End


SELECT {

[MEASURES].[KPI VALUE],

[MEASURES].[KPI GOAL],

[MEASURES].[KPI STATUS],

[MEASURES].[KPI TREND]

}ON 0,

NON EMPTY [Date Of Manufacture].[Hierarchy].CHILDREN ON 1

FROM

[Huffner Textile Cube]


Status is comaparing KpiValue and KpiGoal. KpiValue is the actual Revenue reached in individual years. KpiGoal = Revevenue * 1.15; 15% increase in every year. If there is a decrease of Revenue, but it is less than 15% the KpiStatus is neutral (0). Negative status (-1) means, we reached less than the previous year. Positive status (1) means that an increase happened.

Trend is comparing KpiValue between individual years. Has nothing to do with KpiGoal. If there is a decrease <=.02 in the value (Revenue), KpiTrend is neutral (0), if the decrease is higher, trend is negative, else positive, but must be higher than 2% .



 

KPI Production Cost


WITH MEMBER [MEASURES].[KPI VALUE]

AS

[Measures].[Production Cost]


MEMBER [MEASURES].[KPI GOAL]

AS

CASE

WHEN ISEMPTY(

PARALLELPERIOD(

[Date Of Manufacture].[Hierarchy].[Year],1, [Date Of Manufacture].[Hierarchy].CURRENTMEMBER))

THEN [Measures].[Production Cost]

ELSE 1.10 *

([Measures].[Production Cost],

PARALLELPERIOD([Date Of Manufacture].[Hierarchy].[Year],1, [Date Of Manufacture].[Hierarchy].CURRENTMEMBER))

END

MEMBER [MEASURES].[KPI STATUS]

AS

CASE

WHEN [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] > 1 THEN -1

WHEN [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] <= 1 THEN 1

ELSE 0


END


MEMBER [MEASURES].[KPI TREND]

AS

--WHEN COST_LY = NULL THEN 0

CASE

WHEN ISEMPTY(PARALLELPERIOD([Date Of Manufacture].[Hierarchy].[Year],1,[Date Of Manufacture].[Hierarchy].CurrentMember))

THEN 0

--((COST - COST_LY) / COST_LY)<= 0.1 = POSITIVE

WHEN

VBA!Abs((KpiValue( "Production Cost" ) -

(KpiValue( "Production Cost" ),

PARALLELPERIOD([Date Of Manufacture].[Hierarchy].[Year],1,[Date Of Manufacture].[Hierarchy].CurrentMember)))

/

(KpiValue( "Production Cost" ),

PARALLELPERIOD([Date Of Manufacture].[Hierarchy].[Year],1,[Date Of Manufacture].[Hierarchy].CurrentMember)))

<= 0.1

THEN 1

--((COST - COST_LY) / COST_LY)>0.1 = NEGATIVE

WHEN

VBA!Abs((KpiValue( "Production Cost" ) -

(KpiValue( "Production Cost" ),

PARALLELPERIOD([Date Of Manufacture].[Hierarchy].[Year],1,[Date Of Manufacture].[Hierarchy].CurrentMember)))

/

(KpiValue( "Production Cost" ),

PARALLELPERIOD([Date Of Manufacture].[Hierarchy].[Year],1,[Date Of Manufacture].[Hierarchy].CurrentMember)))

> 0.1

THEN -1

ELSE 0

END

SELECT {

[MEASURES].[KPI VALUE],

[MEASURES].[KPI GOAL],

[MEASURES].[KPI STATUS],

[MEASURES].[KPI TREND]

} ON 0,

[Date Of Manufacture].[Hierarchy].CHILDREN ON 1

FROM [Huffner Textile Cube]


KpiGoal is 10% more than the the previous year value.

KPIStatus (green frame) is comparing KpiValue and KpiGoal. The question is: Did we reach the goal? The logic defined behind the status says:


WHEN [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] > 1 THEN -1

WHEN [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] <= 1 THEN 1

If the cost is higher than the goal is: Negative, else Positive.


KpiTrend is looking at the actual Production Cost values and comparing current year with the previous year. Explaining whether the business is growing or falling down. The paradox is that if the costs are too high,if growing, the business is falling. Logic behind the KpiTrend measure is as follow:

((COST - COST_LY) / COST_LY)<= 0.1 = POSITIVE (Y-O-Y GROWTH IS LESS THAN 10%)

((COST - COST_LY) / COST_LY)>0.1 = NEGATIVE (Y-O-Y GROWTH IS MORE THAN 10%)

else = NEUTRAL


 

KPI Product Category Gross Profit Margin


WITH MEMBER [MEASURES].[KPI VALUE]

AS

[Measures].[Gross Profit Margin]


MEMBER [MEASURES].[KPI GOAL]

AS

Case

When [Product].[Product Category].CurrentMember Is [Product].[Product Category].[Product Category].&[1]

Then .38

When [Product].[Product Category].CurrentMember Is [Product].[Product Category].[Product Category].&[2]

Then 0.38

When [Product].[Product Category].CurrentMember Is [Product].[Product Category].[Product Category].&[3]

Then 0.39

When [Product].[Product Category].CurrentMember Is [Product].[Product Category].[Product Category].&[4]