# Optional Power BI implementation

The web dashboard works independently. These portable tables and measures support an optional native Power BI report, which must be built and tested in Power BI Desktop outside this environment. They are not a PBIX file.

## Tables
Import the CSV files from `model/` and name the tables SalesMonthly, OrderMembership, InventoryYearEnd, Years and Categories. Set Year, Quarter, CellID, OrderID, ProductKey and quantities as whole numbers; financial amounts as fixed decimal; Month as text; RecordDate as date. Do not publish source order identifiers as customer information. The OrderID in this package is a generated integer membership key.

## Relationships
Use one-to-many, single-direction filters:
- Years[Year] to SalesMonthly[Year].
- Years[Year] to InventoryYearEnd[Year].
- Categories[Category] to SalesMonthly[Category].
- Categories[Category] to InventoryYearEnd[Category].
- SalesMonthly[CellID] to OrderMembership[CellID].

SalesMonthly has exactly one row per CellID. Filter channel, country and quarter from SalesMonthly. Those controls must not filter InventoryYearEnd. Use Years and Categories for the shared slicers. Use a single reporting year for the executive page. Reseller coverage ends on 29 November 2013. Suppress full-year and Q4 comparisons for that channel or both channels, as the web dashboard does. Add this coverage rule before presenting a native report as equivalent.

## Measures
```dax
Sales = SUM(SalesMonthly[Sales])
Product Cost = SUM(SalesMonthly[ProductCost])
Gross Contribution = [Sales] - [Product Cost]
Contribution Margin = DIVIDE([Gross Contribution], [Sales])
Orders = DISTINCTCOUNT(OrderMembership[OrderID])
Units Sold = SUM(SalesMonthly[Units])
Stock Positions Below Reorder =
    COUNTROWS(FILTER(InventoryYearEnd,
        InventoryYearEnd[UnitsBalance] < InventoryYearEnd[ReorderPoint]))
Prior Year Sales =
    VAR ReportingYear = SELECTEDVALUE(Years[Year])
    RETURN IF(NOT ISBLANK(ReportingYear),
        CALCULATE([Sales], REMOVEFILTERS(Years), Years[Year] = ReportingYear - 1))
Sales Change = DIVIDE([Sales] - [Prior Year Sales], [Prior Year Sales])
```

The same quarter/channel/country/category filters remain during the prior-year calculation. Month-specific comparisons need a proper calendar model or a matching month-number filter; the above prior-year measure is for year/quarter comparisons.

## External checks
Compare all-year totals with validation.json for 2011, 2012 and 2013. Test category-overlap order counting, empty filters, quarter-to-quarter prior-year comparisons and inventory isolation. Test relationships, refresh and report visuals in Desktop before describing a native Power BI report as complete. The measures above have not been executed in Power BI Desktop here.