pivotronics

As a seasoned Power BI analyst you most probably would come across a requirement where you have to display semi-additive measures in Power BI visuals.

Scenario: Here is an example scenario and the solution in Power BI. Let’s say the organizational hierarchy of a company is segregated into 3 levels – Lines of Business, Divisions and Functions. Some kind of goals are defined for each of the org levels in the company. However, the overall goal for each level in the org hierarchy is not simply the sum of the goals of the underlying sub-levels in the org hierarchy as shown in the below fictitious example:

From the above example, clearly goal of Global Product Delivery division is not equal to sum of all sub-levels within that division (i.e., sum of the goals of Treasury function and Investment Analytics function). Instead it is listed as a separate line item with goal as 4. Similar thing is happening for Global Process Delivery division too.

Requirement: The above goals table and a date dimension table in Power BI Desktop are as below:

When you place slicer visuals for Line of Business, Division and Function and Goal is displayed in the Power BI report page, we need to show 4 as goal for Global Product Delivery and not 9 (which is goal of Treasury (i.e., 5) + goal of Investment Analytics (i.e., 4)). Goal can be considered as a semi-additive or non-additive measure in this context.

Now how do you display this kind of a measure in the visual? The magic lies in DAX. You can write a measure for Goal that displays the goal from the explicit overall goal line item of that hierarchy level in the Goals table when the selection in the slicers is resulting in overall level otherwise display the actual goal visible in the context. This is to be done for each level in the org hierarchy. For the example above, you need to do this for two levels in the org hierarchy – Line of Business and Division.

Note that you are required to display these goals month over month as a line chart and goals remain the same across year. i.e., there is no relationship between “Date or Calendar” dimension table and this “Goals” table in the Power BI model. If you don’t already have a date dimension at your end, you can download the Power BI workbook shared in this blog post that uses the date dimension table created using Power Query M custom function written to facilitate date dimension creation.

Solution: First create a simple measure with the name “Goals” that is simply the sum of values in “Goal” in the table. This you can use it later in the final goal measure so that you don’t have to write the DAX for sum of goals every time you intend to show sum of goals.

Goals = SUM(Goals[Goal])

Now, you need to create a measure using DAX for Goal as below. Comments are in green.

Business Goal = VAR SelectedDivision = SELECTEDVALUE(Goals[Division]) //Division selected in slicer
VAR SelectedFunction = SELECTEDVALUE(Goals[Function]) //Function selected in slicer
RETURN IF(ISBLANK(SelectedDivision) //if division selected in slicer in the current context is blank, it means nothing is selected. In this case, we need to show the goal where division is "ALL" from the table. This is overall division level goal.
            , CALCULATE([Goals]
                        , Goals[Division] = "ALL"
                        )
            , IF(ISBLANK(SelectedFunction) //if function selected in slicer in the current context is blank, it means nothing is selected. In this case, we need to show the goal where function is "ALL" from the table. This is overall functioin level goal.
                , CALCULATE([Goals]
                            , Goals[Function] = "ALL"
                            )
                , [Goals] //else show the goal for the selected division and function from goals table as is.
                )
        )

Now see the simple line chart displaying the goal correctly (you can validate these results looking at the goals shown in the initial table diagram above) for each of the overall levels selection from the slicers:

Below visual shows the overall goal of “Global Delivery” as 4.5

Below visual shows the overall goal of “Global Process Delivery” as 4

Caveat: One caveat to this is what if we have blank values coming in the Division and Function columns. DAX will not be able differentiate such nulls and the above solution will not work. Hence, before using this solution, it is important to replace any such nulls with some custom text that you could identify later in DAX measure to handle it explicitly. In this example, it’s coming up as a text “ALL”.

Hope that’s learning for the day. See you soon with another data challenge write-up.

The pbix file showing this solution can be downloaded from here.