• Marek Vavrovic

HuffnerTextile - Multidimensional Model

Updated: Mar 31


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]

Then 0.41

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

Then 0.42

Else .37

End


MEMBER [MEASURES].[KPI STATUS]

AS


Case

When isempty([MEASURES].[KPI VALUE]) then null

When [MEASURES].[KPI VALUE] /

[MEASURES].[KPI GOAL] >= .99

Then 1

When [MEASURES].[KPI VALUE] /

[MEASURES].[KPI GOAL] < .99

And

[MEASURES].[KPI VALUE] /

[MEASURES].[KPI GOAL] >= .97

Then 0

Else -1

End


MEMBER [MEASURES].[KPI TREND]

AS

CASE

WHEN ISEMPTY([MEASURES].[KPI STATUS])

THEN 0

--Y-o-Y <= 5% neutral

WHEN

VBA!Abs(([MEASURES].[KPI VALUE] -

([MEASURES].[KPI VALUE],

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

/

([MEASURES].[KPI VALUE],

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

<=.005

THEN 0

--Y-o-Y > 5% positive

WHEN

VBA!Abs(([MEASURES].[KPI VALUE] -

([MEASURES].[KPI VALUE],

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

/

([MEASURES].[KPI VALUE],

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

> .005

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].[Year],

[Product].[Product Category].[Product Category]

)ON 1

FROM [Huffner Textile Cube];


The gross profit margin is the percentage of sales revenue that is left once the cost of sales has been paid. It tells a business how much gross profit is made for every pound of sales revenue received. For example, a gross profit margin of 40% means that every pound of sales provides 40 pence of gross profit.


 

KPI Freight Cost


WITH MEMBER [MEASURES].[KPI VALUE]

AS

[Measures].[Freight Cost]


MEMBER [MEASURES].[KPI GOAL] --GOAL IS 5% INCREASE (Y-O-Y)

AS

CASE

WHEN ISEMPTY(

PARALLELPERIOD(

[Delivery Date].[Hierarchy].[Year],1, [Delivery Date].[Hierarchy].CURRENTMEMBER))

THEN [Measures].[Freight Cost]

ELSE 1.05 *

([Measures].[Freight Cost],

PARALLELPERIOD([Delivery Date].[Hierarchy].[Year],1, [Delivery Date].[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([Delivery Date].[Hierarchy].[Year],1, [Delivery Date].[Hierarchy].CURRENTMEMBER))

THEN 0

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

WHEN

VBA!Abs(([MEASURES].[KPI VALUE] -

([MEASURES].[KPI VALUE],

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

/

([MEASURES].[KPI VALUE],

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

<= 0.05

THEN 1

--((COST - COST_LY) / COST_LY)>0.05 NEGATIVE

WHEN

VBA!Abs(([MEASURES].[KPI VALUE] -

([MEASURES].[KPI VALUE],

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

/

([MEASURES].[KPI VALUE],

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

> 0.05

THEN -1

ELSE 0

END

SELECT {

[MEASURES].[KPI VALUE],

[MEASURES].[KPI GOAL],

[MEASURES].[KPI STATUS],

[MEASURES].[KPI TREND]

} ON 0,

NON EMPTY [Delivery Date].[Hierarchy].CHILDREN ON 1

FROM [Huffner Textile Cube];




 

KPI Revenue Sub Depot


WITH MEMBER [MEASURES].[KPI VALUE]

AS

[Measures].[Revenue Sub Depot]


MEMBER [MEASURES].[KPI GOAL]

AS

--GOAL IS 10 % INCREASE IN EACH YEAR

Case

When IsEmpty(ParallelPeriod

([Ship Date].[Hierarchy].[Year],1,

[Ship Date].[Hierarchy].CurrentMember

))

Then [Measures].[Revenue Sub Depot]

Else 1.1 *

( [Measures].[Revenue Sub Depot],

ParallelPeriod

([Ship Date].[Hierarchy].[Year],1,

[Ship Date].[Hierarchy].CurrentMember

))

End

,format_string="currency"


MEMBER [MEASURES].[KPI STATUS]

AS

Case

When isempty([MEASURES].[KPI VALUE]) Then 0

When [MEASURES].[KPI VALUE] / [MEASURES].[KPI GOAL] > 1 Then 1 --POSITIVE

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

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

Then 0

Else -1 --NEGATIVE

End


MEMBER [MEASURES].[KPI TREND]

AS

CASE

WHEN ISEMPTY([MEASURES].[KPI VALUE]) THEN 0

WHEN [MEASURES].[KPI VALUE] < [MEASURES].[Sub Depot Sales_LY] THEN -1

WHEN [MEASURES].[KPI VALUE] > [MEASURES].[Sub Depot Sales_LY] THEN 1

ELSE 0

END


SELECT {

[MEASURES].[KPI VALUE],

[MEASURES].[Sub Depot Sales_LY],

[MEASURES].[KPI GOAL],

[MEASURES].[KPI STATUS],

[MEASURES].[KPI TREND]

} ON 0,

NON EMPTY(

[Ship Date].[Hierarchy].[Year],

[Warehouse].[Warehouse].[Warehouse]

) ON 1

FROM [Huffner Textile Cube]


KPI GOAL is 10% higher sales then the last year was. (Sub Depot Sales_LY * 1.1)

KPI STATUS is defined as a ratio of KpiValue and KpiGoal

If > 1 Positive

If <= 1 and >= .95 Neutral

Else Negative

KPI TREND is also defined as a ratio of KpiValue and Sub Depot Sales_LY (Current and Previous Year)


 

KPI Two-Year Sales Growth


WITH MEMBER [MEASURES].[KPI VALUE]

AS

[Measures].[Total Due Client]


MEMBER [MEASURES].[KPI GOAL]

AS

--GOAL IS 10 % INCREASE IN EACH YEAR

CASE

WHEN IsEmpty(ParallelPeriod

([Delivery Date].[Hierarchy].[Year],1,

[Delivery Date].[Hierarchy].CurrentMember

))

THEN [Measures].[Total Due Client]

ELSE 1.1 *

( [Measures].[Total Due Client],

ParallelPeriod

([Delivery Date].[Hierarchy].[Year],1,

[Delivery Date].[Hierarchy].CurrentMember

))

END


MEMBER [MEASURES].[KPI STATUS]

AS

Case

When isempty([MEASURES].[KPI VALUE]) Then 0

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] >= .95

Then 0

Else -1

End


MEMBER [MEASURES].[KPI TREND]

AS

Case

When IsEmpty

(ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

1,

[Delivery Date].[Hierarchy].CurrentMember

))

Then 0

When

([MEASURES].[KPI VALUE],

ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

2,

[Delivery Date].[Hierarchy].CurrentMember

))

>

([MEASURES].[KPI VALUE],

ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

1,

[Delivery Date].[Hierarchy].CurrentMember

))

AND

([MEASURES].[KPI VALUE],

ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

1,

[Delivery Date].[Hierarchy].CurrentMember

))

>

[MEASURES].[KPI VALUE]

Then -1

When

([MEASURES].[KPI VALUE],

ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

2,

[Delivery Date].[Hierarchy].CurrentMember

))

<

([MEASURES].[KPI VALUE],

ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

1,

[Delivery Date].[Hierarchy].CurrentMember

))

AND

([MEASURES].[KPI VALUE],

ParallelPeriod

(

[Delivery Date].[Hierarchy].[Year],

1,

[Delivery Date].[Hierarchy].CurrentMember

))

<

[MEASURES].[KPI VALUE]

Then 1

Else 0

End


SELECT {

[MEASURES].[KPI VALUE],

[MEASURES].[KPI GOAL],

[MEASURES].[KPI STATUS],

[MEASURES].[KPI TREND]

} ON 0,

[Delivery Date].[Hierarchy].CHILDREN ON 1

FROM

[Huffner Textile Cube];


KpiTrend: There must be 2 years continues growth to indicate a positive trend (1). If is 1 year growth and next year fall, the trend is neutral (0). If there are two consecutive years with decreasing quantity, the trend is negative (-1).


7 views0 comments