How to Realize Dynamic Asymmetric Reports in Power BI / Excel

bykaraoan
on ‎08-28-201805:29 PM - last edited on ‎08-30-201810:42 AM by MindyJ

In this blog we cover how to realize asymmetric reporting requirements dynamically in a Power BI Matrix or Pivot Table. This is a typical requirements in financial forecasting and reporting.

What is an asymmetric report?

An asymmetric report uses in either rows or column a combination where the child node content differs between the parent nodes. For example, for a nested two-dimensional display of "Time" with members 2018, 2019, 2020 and "Scenario" with members "Actual", "Budget" and "Forecast" we only want to display some of the scenarios for each year as opposed to all three for each year:

Normally, the Pivot Table or Power BI Matrix will display the nodes symmetrically so you can only show all scenarios for each year. In the Pivot Table there is a way out with using MDX sets for the sources that support it (e.g. PowerPivot, SSAS MD) but this isn't supported in either SSAS Tabular or SQL sources that are very often used in this context. Your other option is a static DAX measure that filters the required combination. This has a variety of limitations e.g. drill down won't be available anymore and it's not easy to make it dynamic. To define an asymmetric report, your first step is to put the two fields in the matrix or Pivot Table:

We can see Scenario and Year fields are added to the columns of the Matrix, the visual shows all the available combinations of scenario/years (as zero suppression is turned on not all available combinations will show). This however, is not what we want. We only want to show the Budget for 2019. To achieve the desired report layout, we can add a few DAX measures to get the job done. Our method relies on specifying in a table what combinations we want to show and to ignore the rest. To do this, we will produce blanks for the value field, if the combination is not one of the specified combinations.

Configuring The Report

Step 1: How to specify the desired combinations

We just need to add a column in one of the respective dimension tables that specifies the desired combination for that element.

In our case we have two dimensions and need to specify the required combinations from: "Scenario", and "Years". In one of these dimensions we add a column to specify the desired combination. You can achieve this by creating a data entry table in Power BI, a linked table in Excel or use a solution like Acterys that enables you to edit dimensions and add dimension table columns directly in the model. In our example we have added a column called “Report Year” to the Scenario dimension. We have specified which year we want to be associated with a certain scenario. You can use multiple columns or values if needed. In this case, for the sake of simplicity we use one year for each scenario.

Step 2: The DAX Measure to Show or Hide a Combination:

Basically, we need a measure in an IF function, to produce blanks if encountering an undesired combination and a normal summation otherwise. First, we need a measure to check if the desired combination is being encountered: showYear = IF(max(DimYear[Year]) in VALUES(DimScenario[Report Year]),TRUE(),FALSE()) In the above measure, we check if the year-scenario combination is a valid one. To show the effect of this measure, let’s display this measure in the matrix:

Only the valid Year/Scenario combinations produce a "True" value. The next step is easy. We just need to create a measure to check the “ShowYear” flag, and if "True" do a normal sum and if false pass a blank. The blanks get suppressed by the matrix visual/Pivot Table.

Report Value = if([showYear],sum(GL[Value]),BLANK())

The end result:

The Excel and Power Bi files of this example can be downloaded from here: Sample Download