• Marek Vavrovic

HuffnerTextile - Data Visualisation PowerBI Desktop


PowerBI Retail Sales Dashboard

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:

  1. 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))



13 views0 comments

Recent Posts

See All