Did You Know? Series: 2nd edition - Grasping Advanced Analytics with Power BI

Written By Pooja Dham

Dear Reader,

Welcome to the second installment of my Did You Know? series! 🚀
This series is my way of sharing valuable insights and learnings in Power BI with all of you.

Last month I spotlighted VAR—a powerful yet often underutilized feature in Power BI that can make your DAX formulas cleaner, faster, and easier to understand. If you missed it, feel free to explore it on LinkedIn or on my website.

Today I am diving into Scenario Analysis in Power Bi. Why should you choose Power BI over Excel for this?  Here are the key benefits:

1. Interactivity and Visualization

  • Power BI: Enables dynamic and interactive visualizations. Users can create slicers, filters, and dashboards to explore different scenarios visually and understand their impact in real time. For example, a user can adjust a slicer for sales growth percentage and instantly see how it affects revenue projections across regions or products.

  • Excel: While charts and tables can represent data, interactivity is limited. Users need to manually adjust values, use drop-down lists, or create multiple what-if scenarios.

2. Centralized Data Model

  • Power BI: All the data is stored in a centralized model. Relationships between tables (e.g., Products, Customers, and Sales) are predefined, and calculations automatically reflect those relationships. This ensures consistency and reduces errors.

  • Excel: Requires manually managing relationships, often through VLOOKUP, INDEX-MATCH, or complex formulas. This can be error-prone and harder to scale.

3. Automation and Scalability

  • Power BI: Automatically updates data and recalculates scenarios when connected to live data sources. It can handle large datasets from databases, cloud services, or APIs seamlessly.

  • Excel: Requires manual refresh or import of data. Managing large datasets often leads to performance issues or file size limitations.

4. Dynamic and Advanced Analytics

  • Power BI: Supports DAX (Data Analysis Expressions), which allows creating advanced measures for dynamic calculations like Top N analysis, running totals, or comparisons between scenarios. It also integrates with AI features for predictive analytics.

  • Excel: Advanced calculations are possible but may require nested formulas, making them harder to audit and maintain.

5. Multi-Dimensional Analysis

  • Power BI: Handles multidimensional data effectively. Users can drill down into hierarchies (e.g., Year → Quarter → Month → Day) or across dimensions (e.g., Product → Region → Channel) for detailed scenario exploration.

  • Excel: Requires creating pivot tables and manually restructuring data for multidimensional analysis, which can be cumbersome.

Real-World Applications of Scenario Analysis in Power BI

1. Price Modeling Tool – Using Power BI, I developed a price modeling tool that efficiently handles multiple scenarios, essential for structuring price hierarchies—from retail shelf price to manufacturer gross margin. Check out my separate newsletter for more details.

2. Sales Forecasting for multiple products – I created a Power BI report allowing users to dynamically adjust forecast percentages for multiple products using parameters (click here to view the report in pdf).

Here are the steps I used to calculate the forecast sales:

·        Set up Parameters for Products

·        Calculate This Year (TY) Sales

·        Calculate Last year Sales (LY) using the “DateAdd” function

·        Calculate Average Sales for TY and LY as the base for forecasting

·        Calculate Forecast Sales using the VAR function (refer formula in the appendix below)

3. Time Intelligence for Forecasts I also used time intelligence functions to forecast metrics like sales and profit. Here's an example formula from one of my projects:

2025 Forecasts =

CALCULATE( [Total Sales],

    FILTER( ALL( Dates ), Dates[Year] = 2024 ),

        DATEADD( Dates[Date], -1, YEAR ) ) * 1.05

Take Your Analytics to the Next Level

With Power BI, scenario analysis becomes not only efficient but also highly effective, enabling you to explore, iterate, and derive insights like never before.

I hope you found this article helpful! Let me know your thoughts, and I’ll see you next month.

Appendix – Sales Forecast using VAR functionality

Forecast Sales =

VAR Forecast_Gin =

    Calculate([Avg.Sales_TY_LY]*(1+SELECTEDVALUE(Forecast_Gin[Gin Forecast])),

    'Brand_Item lookup'[Brands (groups)] = "ABC Gin"

    )

VAR Forecast_Rum =

    Calculate([Avg.Sales_TY_LY]*(1+SELECTEDVALUE(Forecast_Rum[Forecast_Rum])),

    'Brand_Item lookup'[Brands (groups)] = "ABC Rum"

    )

VAR Forecast_Tequila =

    Calculate([Avg.Sales_TY_LY]*(1+SELECTEDVALUE(Forecast_Tequila[Forecast_Tequila])),

    'Brand_Item lookup'[Brands (groups)] = "ABC Tequila"

    )

VAR Forecast_Vodka =

    Calculate([Avg.Sales_TY_LY]*(1+SELECTEDVALUE(Forecast_Vodka[Forecast_Vodka])),

    'Brand_Item lookup'[Brands (groups)] = "ABC Vodka"

    )

VAR Forecast_Whiskey =

    Calculate([Avg.Sales_TY_LY]*(1+SELECTEDVALUE(Forecast_Whiskey[Forecast_Whiskey])),

    'Brand_Item lookup'[Brands (groups)] = "ABC Whiskey"

    )

VAR Total_Forecast =

    Forecast_Gin +

    Forecast_Rum +

    Forecast_Tequila +

    Forecast_Vodka +

    Forecast_Whiskey

RETURN

IF(

    ISFILTERED('Brand_Item lookup'[Brands (groups)]),

    SWITCH(

        SELECTEDVALUE('Brand_Item lookup'[Brands (groups)]),

        "ABC Gin", Forecast_Gin,

        "ABC Rum", Forecast_Rum,

        "ABC Tequila", Forecast_Tequila,

        "ABC Vodka", Forecast_Vodka,

        "ABC Whiskey", Forecast_Whiskey,

        BLANK()  // Handle unforeseen brands gracefully

    ),

    Total_Forecast  // Return total forecast when no specific brand is selected

)

Pooja Dham https://www.intalyzeconsulting.com
Next

A Smarter Pricing Model for the Beverage Industry – Tailored to Your Needs