Sometimes it is the simplest ideas that can add value – I think these ideas are two good examples of that.

I was working with a client this week helping them with their Power BI project.  We are building tools to analyse General Ledger data that we have made available in Power BI.    It is very common with GL data to have multiple versions (or scenarios) of the data loaded into the fact table (eg budget, actual, forecast as at Q2, forecast as at Q3 etc).  It is therefore essential that the data is first “filtered” by one of the scenarios at all times – it makes no sense to sum up the values in multiple scenarios at any time.

I have created a simple sales forecasting scenario workbook below where I have several different versions of the data loaded in my fact table (a budget and 2 different versions of a forecast).  See how in the image below the numbers on the right are an aggregation of multiple scenarios – these numbers have no meaning at all to anyone and the data should never be viewed this way.

image-1024x373You must select a single scenario

It therefore follows that you must always select a single scenario when look at this data.  The default slicer setting in Power BI is a ”single select” slicer (shown below) however it is still possible for a user to multi select by holding control on the keyboard and selecting more than 1 scenario.

image

More on the trick to how to prevent that later

Think Outside your Old Paradigms when using Power BI

I am a big believer in finding ways to add value to the visualisations you build by leveraging the features and capabilities that are available. If you just try to replicate what you have always done in Excel, you will be missing out on opportunities to add additional value with the Power BI tools.

Compare the 2 different approaches to slicing data below.  The one on the left is using a standard Power BI slicer and this works just fine – just like it would with a Pivot Table in Excel using a slicer.

image-2-1024x385

The example on the right adds more value over the one on the left.  The example on the right uses a column chart instead of a slicer.  The benefit of this is you can communicate more information to the user than you can with the static slicer.  In this case I am displaying the total value of each of the scenarios which means it is easy to see the relative difference between them.  And this all happens while still providing slicer capabilities thanks to the powerful cross filter feature in Power BI.  This is what I mean by adding value by thinking outside your old paradigms.

slicer_thumb

How to Prevent Confusion with the Users

Now there is a new issue that has been introduced by the “added value” column chart slicer shown above.  The problem is that slicers can be published to Power BI with a single slicer value selected and single select turned on.  It is not possible to publish a column chart with one column pre-selected.

image-3-1024x383

So do you see the problem here?  By “adding value” to the user and swapping out the boring slicer in the report on the left with a value adding column chart on the right, it could create confusion by aggregating all the scenarios into a non-sensical hash total.

IFHASONEVALUE and Cards to the Rescue

To solve this problem, I created a warning message to display to the user.  This warning message is set to display when ever there is more than 1 scenario selected – it disappears when the user selects a single scenario.   The DAX for this is really quite easy.

Warning Message= IF(HASONEVALUE(Scenario[ID])," ","You must select a single scenario by clicking the column chart at the top of the page")

You can see how it works below – this is a live embedded interactive report direct from Power BI using the Publish to Web feature.  Click on any single column in the chart to hide the warning message, and click on the same column again to make the message reappear.

I did a couple of things to make this work the way I wanted.  I sent the warning message behind the other visuals using “send to back”. This is important to prevent the user accidentally selecting the message.  Also note that I used a space “ “ instead of BLANK() as the alternate result.  If you use BLANK(), then Power BI will return the word BLANK and that is not what is needed here. By the way, please vote for this idea to change this behaviour where it returns the word BLANK.

And of course this warning message is useful even if you use a regular slicer, to warn a user in case they multi select a regular slicer.

  Subscribe to PowerPivotPro!
X

Subscribe

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here. 

This Post Has 19 Comments

  1. @Matt – this is awesome; love the idea. Can you post the PBIX so we can see the data model to understand where the “Scenario” column lives? Or post the relationships view?

  2. Good tips, Matt!

    Another way would be to set budget as default scenario value using a similar formula as the warning message:
    = IF(HASONEVALUE(Scenario[ID]),SUM(General_Leader[Value]),CALCULATE(SUM(General_Leader[Value]),Scenario[ID]=”Budget”))

    “The warning message” can now be used to inform which scenario that is being used:
    = IF(HASONEVALUE(Scenario[ID]),VALUES(Scenario[ID]),”Budget”)

    Now they can use the chart to slice to another scenario.

      1. Ah yes you are correct 🙂 This will pre-filter the chart and not work as intended in this case.

        What I usually do is to have a disconnected table with an two columns – Index and description and use this as slicer:
        Index – Scenario
        1 – Budget
        2 – Forecast at Q2
        3 – Forecast at Q3

        Assuming this Index column is in the fact table then we can use this to determine the default value.

        –This measure will be 1 as default, but change according to the filter context.
        Slicer Value =MIN(Table[Index])

        –The Total Qty formula would now be like this. The measure will now not filter the slicer chart, but show values for the different scenarios. The table below will show budget until Forecast at Q2 or Forecast at Q3 is chosen.

        =SWITCH ( [Slicer Value],
        1, CALCULATE(SUM(General_Leader[Value]),Scenario[ID]=”Budget”),
        2, CALCULATE(SUM(General_Leader[Value]),Scenario[ID]=”Forecast at Q2”)
        3, CALCULATE(SUM(General_Leader[Value]),Scenario[ID]=”Forecast at Q3”)
        )

  3. Great suggestions! Having multiple scenarios (actual, budget, etc) in a FACT table is extremely common for anyone in a finance organization. Love the “Warning Message” measure!

  4. Very thoughtful post. I have struggled with finding a good way to indicate to the user that they should navigate the report by clicking on a chart. I like this approach…. One might use the custom tool tips that are now available as well – perhaps including a reminder or notice in the tool tip on hover.

  5. I too have found it is the best financial data model strategy to put all scenarios into 1 fact table be it actual, budget, historic. Stack 1 on top of the other all with same columns of data. The flat single table doesn’t optimise memory usage but it makes DAX formulas so much easier by simply
    CALCULATE( [measure],filter(“scenario”)).

  6. What happens if the name of your Scenario changes or you add a Scenario 4. Does this mean you have to manually go back into your Scenario table and add a new line manually?

    1. A scenario is just data in your table, so it is like any other data field (eg like what happens when a new date comes into the data). So if your data provider is set up to handle it, then it should just work. I would normally expect the data to flow through automatically without issue. You should always have any lookup tables to automatically grow based on new data in your data table.

  7. I like the potential of this idea; however, I think it does have a potential limitation. Since right now Power BI doesn’t give you the option to filter by multiple visuals at one time (something I hope can change soon), you would essentially be limited to this one “slicer visual” for this scenario. You would run into issues if you wanted to see the budget by territory, for example. Once you clicked on the territory visual, you’d unfilter the budget version filter.

Leave a Comment or Question