Links to download:
MDX - CALCULATIONS
--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).
Commenti