The world’s leading publication for data science, AI, and ML professionals.

Changing perspective on your data in Power BI

Usually, we need more space on a report page than we have. But what if we can switch the perspective on our data on the same page?

Introduction

Imagine a Report page with some Cards, columns, and line charts.

On top of the page, you will see four buttons:

  • Actuals
  • YTD (Year-To-Date)
  • YE (Year-End)
  • Last three months

Something like this:

When you click on the Button YTD, your numbers will change to show the YTD results, thus changing the perspective on our result:

This is what we want to achieve.

Lastly, we want to change the format of our results based on the selection.

It’s important to understand that we want a Date or a Year/Month Slicer for our Report. We want to change the result for structural data, like:

  • Geography
  • Stores
  • Product Categories
  • Customer Groups
  • Etc.

Usually, it doesn’t make sense to select a Date Period in a Slicer, like a Year or a Month, and then display the data in a time series showing the Years or Months. We could end up seeing only one instance of the data.

OK, you can select one year to see the result by month. But this is different from the intended scenario.

Prepare the Data model

The first step is to add a scenario table. This table contains one row for each needed scenario.

I create this table in Power Bi with the Enter Data feature to get this table:

This table has no relationship to any other table. This way, I avoid any side effects when using it.

Now, I need to create one Measure for each scenario.

I will not dig into how to write each Measure, as it would make this piece too long.

It is possible to contain all logic into one single Measure. But I have a few reasons to create separate Measures:

  1. Reusability: These Measures can be reused in other reports
  2. Simplicity: Each Measure is simple on its own. It is not necessary to combine several Measures into one Measure
  3. Testability: It is much easier to test each Measure independently.

The next step is creating a Measure that returns the correct result in dependency to the selected scenario.

Adding dynamic Measures

Now I can add the Measure, which can dynamically use the correct Measure based on the selected item in the Slicer.

This Measure is simple:

Online Sale (By scenario) =
VAR SelectedScenario = SELECTEDVALUE('Reporting Scenarios'[Scenario])

RETURN
  SWITCH(SelectedScenario
    ,"Actuals", [Sum Online Sales]
    ,"YTD", [Online Sales (YTD)]
    ,"YE", [Online Sales (YE)]
    ,"Last three Months", [Online Sales (Last 3 Months)]
  )

I add a Slicer to my report page, set its Style to Tile, and enable Single Select to ensure that one value is always selected and never more than one is selected.

Here is what I get when I add the separate Measures and the dynamic Measure to a Matrix Visual and select YTD:

I check the results with the date hierarchy for testing to see if all the calculations are correct.

Using Calculation Groups

To make things more generic, I add two Calculation Groups:

The first contains all the Time Intelligence functions I need to use in our scenario.

Regardless of whether you want to use this approach, I recommend using Calculation Groups for Time Intelligence logic. This way, we can program your logic only once and reuse it with all our Measures.

If you need to learn how to use the Calculation group, jump to the References Section below to find the link to the introductory article by SQLBI.

The second Calculation Group has only one Calculation Item:

VAR SelectedScenario = SELECTEDVALUE('Reporting Scenarios'[Scenario])

RETURN
  SWITCH(SelectedScenario
    ,"Actuals", SELECTEDMEASURE()
    ,"YTD", CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Time Functions] = "YTD")
    ,"YE", CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Time Functions] = "YE")
    ,"Last three Months", CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Time Functions] = "Last three Months")
    ,SELECTEDMEASURE()
  )

The last line ensures a result is returned, even when no Scenario or more than one Scenario is selected.

Here is how it looks in Tabular Editor:

Lastly, I change my Online Sale (By scenario) Measure to the following to use the new Calculation Group:

Online Sale (By scenario) =
  CALCULATE([Sum Online Sales]
            ,'By Scenario'[Scenario] = "By Scenario"
            )

When I add two Matrix Visual side by side, one using the Time Intelligence calculations and one with the Dynamic Measure, the results are as expected:

So, the first step is accomplished. Let’s look at how we can work with formatting the results.

What about dynamic formatting

Imagine that we want to select if we’re going to see the actual result, the variance since the previous year in absolute numbers, or the variance in a percent:

For this case, I extend the Reporting Scenario table with a new Type column and a few new rows:

Now I can add a further Slicer and restrict the available options with the Type column for each slicer to show only those needed for the actual needs with the help of the Filter Pane:

Next, I add two more calculation Items to my Calculation Group for Time Intelligence:

  • YoY: Year-over-Year difference in absolute numbers
  • YoY %: Year-over-Year difference as a percentage

A Calculation Item gets the number format from the source measure by default.

As the Measure used with the Calculation Item has a numeric format, it will return a numeric result instead of a percentage.

But I add a Format String to the Calculation Item for the percentage result, like this, to solve this issue:

"0.00%;-0.00%;0.00%"

This is what I do for the Calculation Item for "YoY %":

I add two more cases to the Calculation Items for the Dynamic Scenario:

VAR SelectedScenario = SELECTEDVALUE('Reporting Scenarios'[Scenario])

RETURN
    SWITCH(SelectedScenario
        ,"Actuals", SELECTEDMEASURE()
        ,"YTD", CALCULATE(SELECTEDMEASURE(),  'Time Intelligence'[Time Functions] = "YTD")
        ,"YE", CALCULATE(SELECTEDMEASURE(),  'Time Intelligence'[Time Functions] = "YE")
        ,"Last three Months", CALCULATE(SELECTEDMEASURE(),  'Time Intelligence'[Time Functions] = "Last three Months")
        ,"Absolute YoY", CALCULATE(SELECTEDMEASURE(),  'Time Intelligence'[Time Functions] = "YoY")
        ,"Relative YoY", FORMAT(CALCULATE(SELECTEDMEASURE(),  'Time Intelligence'[Time Functions] = "YoY %"), "0.00%;-0.00%;0.00%")

        ,SELECTEDMEASURE()
    )

Unfortunately, the Format String from the Calculation Item is not applied to the Calculation Item for the Scenario. For this reason, we must use the FORMAT() function to set the Formatting as a percentage explicitly.

The (side-by-side view with the explicit Measures) result is the following:

When I add a Slicer for the Period and change the Matrix to report by the Product Hierarchy, the result is close to the final version:

Now I can add more Visuals with other dynamic Measures to increase the information density of my report without adding a multitude of Visuals.

Conclusion

In this piece, I combined several techniques to achieve something new:

  1. A Table without Relationships
  2. A Calculation Group for Time Intelligence
  3. A Calculation Group for the Scenarios using Calculation Items from another Calculation Group
  4. Formatting the Output within a Calculation Item

Such a combination of features is vital to find the best solution for a challenge.

When I investigated the possible approaches to solving this problem, I had a clear idea of how to do it.

Nevertheless, I searched the Web for other approaches and found another method using a calculated table for all the dates for each scenario.

As explained in this exemplary video:

But my requirements needed a more flexible approach. Each scenario used in the calculated table had only the date ranges for each scenario based on the actual date. This date will be the date of the last Refresh.

Only if you refresh your dataset you will have the right results. Without a refresh, you will end up having the wrong results.

But I need to be able to define the date range to report dynamically and then set the required calculation method, like YTD or Previous Year.

When I select a possible solution for a problem, I consider the following essential considerations:

  1. Does the selected approach fulfill all requirements?
  2. Does it introduce any restrictions?
  3. Does it introduce any unneeded complexity?
  4. Is it generic enough to be reused?

I hope that this advice can help you for future challenges.

References

Introduction to Calculation Groups by SQLBI: https://www.sqlbi.com/articles/introducing-calculation-groups/

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

Get an email whenever Salvatore Cagliari publishes.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code:

Any support is greatly appreciated and helps me find more time to create more content for you.

Thank you a lot.


Related Articles