Parameter Tables in Power BI for Scenario Modeling

In my initial post, I discussed the programming capabilities in Power BI for advanced analytics. Now, I want to start delving into a few practical examples based on my previous experiences as a Sales and Pricing Analyst. Today, I’m starting with Power BI functionality known as Parameter Tables, which involves the use of a calculated table in DAX and the SELECTEDVALUE function.

Parameter tables are a dynamic tool for manipulating measures in Power BI, offering you the ability to adjust data in various scenarios. Suppose you’re dealing with sales data. In that case, you can apply differing discount levels to your sales amounts to visualize their potential effects.

To explain how it works, let’s first generate some dummy data:

Sales = 
ADDCOLUMNS(
    DATATABLE(
        "Product Name", STRING,
        "List Price", CURRENCY, // Price before any discount
        "Units Sold", DOUBLE,
        {
            { "Product A", 25, 100 },
            { "Product B", 50, 200 },
            { "Product C", 75, 50 }
        }
    ),
    "List Total", [List Price] * [Units Sold] // Total value of products
)

With this sales data, imagine wanting to know the revenue each product would generate if we applied specific discount levels, such as 0%, 5%, or 10%. To accomplish this, we need to create a parameter table.

Begin by making a basic parameter table with a single column that includes all potential discount rates you wish to examine. Also, create a slicer for the various discount levels.

Discounts = 
DATATABLE(
    "Discount Level", STRING,
    "Discount", DOUBLE,
    {
        { "0%", 0 },
        { "5%", 0.05 },
        { "10%", 0.1 }
    }
)

Now, we’ll use a DAX function called the SELECTEDVALUE function. This function picks the currently selected value in your slicer, meaning the discount rate chosen from your parameter table. Hence, as you select a discount rate, your sales figure automatically adjusts in your report according to the selected discount rate.

Discounted Sales = 
VAR Discount = SELECTEDVALUE ( Discounts[Discount], 0 )
VAR ListTotal = SUM(Sales[List Total])
VAR Result = ListTotal * ( 1 - Discount )
RETURN Result

Finally, add this new measure to your visual. You will be able to see the responsive result corresponding to the selection from your parameter table’s slicer. Now we are finished and a copy of this example can be downloaded here.

There are many other examples we could’ve done, but I simply wanted to explain the concept. By using parameter tables, you can view and compare multiple scenarios on the fly, simply by selecting different parameters in the slicer. This is beneficial in pricing analytics as it enables you to perform what-if analysis effectively and make data-driven decisions.

So, if you haven’t given parameter tables in Power BI a try, I highly recommend exploring this feature.