Menu
Close
contact

Dashboard for monitoring status & financial effecf of improvements for heavy industry giant.

In this post i would like to share a report (the solution is provided in my favirote Power BI) which allows to monitor the status of production improvements being constantly implemented. Repost was built for a large heavy industry company (due to obligations i have to hide the numbers and the name as well as adding slight redesign). Just below you can find interactive report with custom logic which i ll describe later with the solution.

* this dashboard is supposed to be consummed on your desktop/laptop

Prerequisites

To start out let me note a few moments that can be considered roughtly as requirements & limitations in the report:

  • Only SQL views were available due to very strict security policies (this was a serious limit to build a report and transform/shape the data provided)
  • Due to limitation mentioned above i had to move all transformations to PowerQuery level
  • The report was provided to host on Power BI RS server (locally)
  • The actual report contains more pages and structure but here i show only top level view on a single page
  • There was one special requirement: when showing Forecast numbers (in matrix of charts) additional logic was required:
    • Check the column Forecast for the selected year and sum up the numbers if the column is not empty
    • Otherwise check the budget column and apply the same logic
    • Similarly check the plan column (in case Forecast and Budget columns are empty)

Model

So here s the model straight away:

Let me explain the original data and move to the modeling.
Input tables set consists of several tables, namely:

  • Table with improvements (top level view)
  • Next level (one down) comprises documents that are more specific to the areas of implementations (more details on a more granular level)
  • The last (third level) shows numbers for each document (from the previous upper level) and each type of numbers is kept in a separate table:
    • Plan
    • Fact
    • Budget
    • Forecast

Now moving on to the modelling. There was a good bunch of transformations (M code in PowerQuery):

  • Cleaning Improvement table
  • Cleaning Document table
  • Meshing Plan, Fact, Budget, Forecast tables
  • Adding custom attributes to all tables
  • Creating table DOC_YEAR_LINK with attributes from previously created tabs
All of the above i had to move to PowerQuery level since there was limited accessibility to the DB where i could ve moved all the logic of transformations.

Transformations in M code included:

  • Flatteing and joining four tables of Fact, Plan, Budget and Forecast into one single table with doc ID and attributes
  • Making a table which has only three main columns: Improvement ID & Year & Segment (we need this to make the model work)
  • Removing unneccessary columns in all tables, cleaning data (nulls, filtering, grouping)
  • Adding segments to Plan-Fact-Forecast-Budget table (this one was the most trickest transformation in M code)
The last item was trickiest since the original Segment (department) in the Improvement table was not split between several zones of implementation. So i had to add additional table with Segments split to the Plan-Fact-Forecast-Budget table.

As a result you can see the model (screenshot above) which has the following idea:

  • I use a special table DOC_YEAR_LINK to filter the Improvements table according to our YEAR filter
  • Connection between IMPROVEMENTS and DOC_YEAR_LINK tables is bidirectional
  • To bring YEAR filter to the PROJECTION table i use TREATAS function (DAX)
  • To filter
  • I used several more deactivated connections to use them later for other metrics

So the main idea of this model was to bring two main attributes to the special table called DOC_YEAR_LINK, then filter the IMPROVEMENTS table and propagate filters to the PROJECTION table with TREATAS function.

I would call this model UNBALANCED since the main fact table (IMPROVEMENTS) doest really contain all the low level data needed to show & split the numbers & segments of implementation. Though it s needed to capture main input data from the high level table.

Measures

Most of the measures are simple or advanced but one measure i d like to point out:

  • Above requirement here stated that we need to check the Forecast-Budget-Plan (in that order) columns and see which one is not blank and pick up monthly number from that one (the numbers will be used to calculate the Forecast)

So let me show the measure here (where we need to check the Forecast-Budget-Plan) and then run down the logic: 

                                
    VAR _selectedMon = FIRSTNONBLANK('PROJECTION'[_monNum],1)

    RETURN

        CALCULATE(
            SUMX(
                ADDCOLUMNS( 
                    'PROJECTION'
                    ,"@@forecast"
                        ,VAR _forecast = 
                            CALCULATE(
                                SUM(PROJECTION[_forecastSPL])
                                ,FILTER('PROJECTION'
                                    ,'PROJECTION'[UNIDInit] = EARLIER(PROJECTION[UNIDInit])
                                    && 'PROJECTION'[_year] = EARLIER(PROJECTION[_year])))

                        VAR _budget = 
                            CALCULATE(
                                SUM(PROJECTION[_budgetSPL])
                                ,FILTER('PROJECTION'
                                    ,'PROJECTION'[UNIDInit] = EARLIER(PROJECTION[UNIDInit])
                                    && 'PROJECTION'[_year] = EARLIER(PROJECTION[_year])))

                        VAR _plan = 
                            CALCULATE(
                                SUM(PROJECTION[_planSPL])
                                ,FILTER('PROJECTION'
                                    ,'PROJECTION'[UNIDInit] = EARLIER(PROJECTION[UNIDInit])
                                    && 'PROJECTION'[_year] = EARLIER(PROJECTION[_year])))
                        RETURN
                            SWITCH(TRUE()
                                ,NOT(ISBLANK(_forecast))
                                    ,[_forecastSPL]
                                ,ISBLANK(_forecast) && NOT(ISBLANK(_budget))
                                    ,[_budgetSPL]
                                ,ISBLANK(_forecast) && ISBLANK(_budget) && NOT(ISBLANK(_plan))
                                    ,[_planSPL]))
                    ,[@@forecast])
                ,TREATAS(VALUES('YEAR'[_year]),'PROJECTION'[_year])
                ,TREATAS(VALUES(DOC_YEAR_LINK[_segment]),'PROJECTION'[_segment])
                ,FILTER(ALL('PROJECTION'),
                        'PROJECTION'[_monNum] <= _selectedMon)
        )

The logic here s the following:

  • First we capture the month (since this measure is used in the bar chart with monthly bars)
  • In the RETURN statement we manage all calculations that is:
    • We iterate with SUMX over a virtual table created with ADDCOLUMNS
    • In ADDCOLUMNS function we check the SUM of each column (eg Forecast, Budget, Plan) for total year
    • And then check with SWITCH which column is not empty pushing the numbers to the @@forecast column
    • Finally we apply some filters with TREATAS

The shown measure is then used in a wrapper measure where i use additional logic on top conditionally showing numbers for the previous months.

This is a good case of using EARLIER function (just served well for the purpose IMHO) with a virtual table.

There was a good bunch of more calculations but i leave them out of this blog since i picked only one that was interesting and the main purpose of this post is showing the dashboard.