Data Models
Data are loaded from the data warehouse database. Using Import Mode. Can't use DirectQuery Mode for Live Connection because I haven't gotten Active Directory installed on my computer. You need an Account to set up the Getaway and that account needs an access to the data.
For Analysis Services to determine if a user connecting to it belongs to a role with permissions to read data, the server needs to convert the effective user name passed from AD to the gateway and on to the Analysis Services server. The Analysis Services server passes the effective user name to a Windows Active Directory domain controller (DC). The Active Directory DC then validates that the effective user name is a valid UPN on a local account. It returns that user’s Windows user name back to the Analysis Services server.
Supplier Schema
Production Schema
Tables DimProduct, DimProductCategory and DimProductSubcategy and DimColor have been loaded as one joined table to simplify the powerbi model.
Product Defect Schema
Tables DimProduct, DimProductCategory and DimProductSubcategy and DimColor have been loaded as one joined table to simplify the powerbi model.
Sub Depot Sales Schema
Tables DimProduct, DimProductCategory and DimProductSubcategy and DimColor have been loaded as one joined table to simplify the powerbi model. Employee and DimSubDepotEmployee have been joined with DimGeography. DimGeography table is redundant in this data model.
Client Sales Schema
Measures and Visualisation
SUPPLIERS
Huffner Textile company has five suppliers. Each supplier is delivering different category of fabrics. Austria (Nonwoven), Germany (Canvas), Poland (Woven), Ukraine (Knit), Turkey (Synthetic). The visual in the red frame (Supplier A/P) = Supplier Account Payable using bookmarks to switch among 3 independent objects. This technique allows to keep more visual on the dashboard and the interaction among them is not sending a query back to the server.
Most of the measures have been created just by using simple aggregate functions as SUM, COUNT, DISTINCTCOUNT.
Cumulative Revenue Supplier =
IF(
NOT(
ISBLANK(
[Supplier Totaldue]
)),
CALCULATE(
[Supplier Totaldue]
,FILTER(
ALL(DimDate),
DimDate[EOM]<=MAX(DimDate[EOM]))),
BLANK())
Freight Cost Y-o-Y % Growth =
varFreight =[Freight Amount]
varLY_Freight =CALCULATE([Freight Amount],DATEADD(DimDate[Date],-1,YEAR))
varFreightChange= Freight-LY_Freight
varResult = DIVIDE(FreightChange,LY_Freight,0)
return
Result
PRODUCTION
Production consists of five main categories of products divided into additional subcategories. 3800 products in total are a combination of subcategory, colour, sub colour and the product width.
Measures for the red framed visual:
create a table with one column [Ratio] and three values (Error Ratio, Cost Ratio, Gross Profit Ratio)
Production Ratio = SWITCH(TRUE(),
"Cost Ratio" in ALLSELECTED(ProductionRatios[Ratio]),[Cost Ratio Production],
"Error Ratio" in ALLSELECTED(ProductionRatios[Ratio]),[Error Ratio],
"Gross Profit Ratio" in ALLSELECTED(ProductionRatios[Ratio]),[Gross Profit Margin])
2. measure for title
Production Ratio Title = SELECTEDVALUE(ProductionRatios[Ratio],"Cost Ratio")
Gross Profit =
var result = [Revenue Production]-[Production Cost]
return
result
Gross Profit Margin =
var Revenue = [Revenue Production]
var COGS = [Production Cost]
var result = DIVIDE((Revenue-COGS), Revenue,0)
return
result
Gross Profit Y-o-Y % Growth =
var GrossProfit =[Gross Profit]
var Ly_Revenue = CALCULATE([Revenue Production],DATEADD(DimDate[Date],-1,YEAR))
var Ly_Cost= CALCULATE([Production Cost],DATEADD(DimDate[Date],-1,YEAR))
var Ly_GrossProfit = Ly_Revenue-Ly_Cost
var GrossProfitChange = GrossProfit-Ly_GrossProfit
var Result = DIVIDE(GrossProfitChange, Ly_GrossProfit,0)
return
Result
SUB DEPOT
Deliveries are divided into two main groups. Defected, marked as 1 and undetected fabrics, marked as 0 on the fact table. Defected goods are delivered to Africa sub depots, undetected into the rest of the world.
MA: Moving average
Sales 1 Month (MA) =
VAR NumOfMonths = 1
VAR LastCurrentDate =
MAX ( FactSubDepotSales[ShipDate] )
VAR Period =
DATESINPERIOD ( FactSubDepotSales[ShipDate], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( FactSubDepotSales[ShipDate] ),
[Totaldue Subdepots]
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, FactSubDepotSales[ShipDate] )
VAR LastDateWithSales = MAX ( FactSubDepotSales[ShipDate])
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )
Sales % =
var Sales = [Totaldue Subdepots]
var AllSales =
CALCULATE([Totaldue Subdepots],KEEPFILTERS(DimDate),ALL(FactSubDepotSales))
var result = DIVIDE(Sales, AllSales)
return
result
CLIENT
This visualisation is filtered by the Italian sub depot. There are 159 clients and 10 sub depots on this dashboard. The Clustered Bar Chart contains a subpage as a tooltip. If you hoover over a bar you will be able to see the details for individual client sales.
Measure calculates the average number of days since placing an order till delivered to client.
Sale Cycle 4 (ClientOrder to Client) Days = AVERAGEX( FactClientSales, DATEDIFF(FactClientSales[OrderDate],FactClientSales[DeliveryDate], DAY))
Comments