Tag: Power BI Premium

Business Intelligence - BI, Data Visualisation, DAX, Microsoft Fabric, Power BI, Power BI Desktop, Power BI Desktop RS

Understanding Power BI Totals: The Math, the Model, and the Misconceptions

The long-running debate around how Power BI calculates totals in tables and matrices has been part of the community conversation for years. Greg Deckler has kept the topic alive through his ongoing “broken totals” posts on social media, often suggesting that Power BI should include a simple toggle to make totals behave more like Excel. His continued campaign prompted a detailed reply from Daniel Otykier in his article “No More Measure Totals Shenanigans“, and earlier, Diego Scalioni explored how DAX evaluates totals internally in his post “Cache me if you can: DAX Totals behind the scenes“.

This blog brings all those perspectives together from a scientific and comparative angle. It looks at how totals are calculated in Power BI and compares that behaviour with Tableau, Excel, Paginated Reports, and even T-SQL. The goal is not to take sides, but to clear up the confusion around what is happening under the hood.

If you are into podcasts and prefer the audio version of this blog, I got you covered. Here an AI generated podcast for this blog. 👇

Power BI’s Broken Totals – Myth Debunked

Are Power BI Totals Really Broken?

Let’s get one thing clear right at the start, no, Power BI totals are not broken. There is no “it depends” this time. What some interpret as broken behaviour is actually how DAX and the underlying model are designed to work.

This post is not personal, it is purely scientific and technical. While I have great respect for Greg and his significant contributions to the Power BI community, I disagree with the use of the word “BROKEN.” It sounds dramatic but does not reflect the full truth. Totals in Power BI behave exactly as the model and the maths define them to. Want to know why? Keep reading.

Why this matters

When someone with Greg’s influence keeps saying totals are “broken”, it really affects how new users see Power BI. Some even start thinking the tool itself is not reliable, when what they are seeing is actually how different reporting tools do their calculations in different ways.

It helps to know the main calculation styles that these tools use:

  • Cell based: This is what you get in worksheet formulas and classic PivotTables that use Excel ranges. Totals are just simple sums of the shown items, with no model or relationships behind the scene.
  • Model driven: This is how Power BI works and also Excel PivotTables that use the Data Model (Power Pivot) or connect to a tabular dataset. Measures are calculated again for every context, so totals depend on how filters and relationships are set.
  • Query driven: Tools like Paginated Reports work this way. The report runs a query, for example SQL or DAX, gets the dataset, and then sums or averages values in the report design. The author decides how each total should be calculated.
  • Hybrid (query and context driven): Tableau fits in here. It gets the data through a query but also lets you change the level of detail and how totals behave in the visual. So sometimes it acts like a query tool and sometimes more like a model one.

Most of the confusion happens when people compare results from these tools as if they all worked the same way. Once you understand the difference between cell based, model driven, query driven, and hybrid tools, the way Power BI shows its totals starts to make full sense.

The problem that started it

Greg’s long-running example uses a small table with a single column of numbers and a DAX measure like this:

SUMX(SampleData, SampleData[Amount]) - 10

In the total row, the result shows 590, while he expects 580 (two groups of 290 each). Based on that, he argues that Power BI totals are “wrong”.

But DAX is only doing what it is told to do. In this measure, the subtraction of 10 happens after the total amount is calculated, not for each row. If the intention was to take 10 away per row, then the measure should be written like this:

SUMX(SampleData, SampleData[Amount] - 10)

This version gives the expected 580 because the subtraction now happens at the lowest level of detail, which is per row.

This might look like a small detail, but it is exactly where most of the confusion around totals begins. The difference is not about Power BI being wrong; it is about understanding where in the calculation the operation happens.

The math behind it

Before we look at the numbers, let’s first talk about what we are trying to do. We Greg’s small and very simple table that shows some amounts by Category and Colour:

CategoryColourAmount
ARed100
AGreen100
ABlue100
BRed100
BGreen100
BBlue100
Continue reading “Understanding Power BI Totals: The Math, the Model, and the Misconceptions” →
Azure, Fabric Capacity, Microsoft Fabric, Power BI, Power BI Governance

Microsoft Fabric: Revealing Cost-Saving Results from Automating Pause & Resume Fabric Capacity

Microsoft Fabric: Revealing Cost-Saving Results from Automating Pause & Resume Fabric Capacity

If you work in data and analytics, particularly within the Microsoft Data Platform, you have likely heard of Microsoft Fabric and its many capabilities. However, one of the biggest challenges organisations face is managing costs effectively.

In previous blogs and videos, I have covered how to optimise Microsoft Fabric capacity costs by automating the pause and resume process using Logic Apps. This approach ensures that your Fabric capacity runs only when needed, reducing unnecessary expenses. But how much can this method actually save? In this post, I share the real cost-saving results after applying this automation over the past few months.

Why Automate Pause & Resume?

Microsoft Fabric is a powerful platform, but maintaining capacity during off-peak hours can be costly. If your workloads are not running 24/7, you might be paying for unused capacity. By automating the pause and resume process, you can ensure that your environment is active only when required, leading to substantial savings.

When Should Organisations Consider Automation?

Not every organisation requires automated capacity management, but here are some scenarios where it makes sense:

  • Non-Continuous Workloads: If your organisation runs batch processing, reporting, or analytics workloads that do not need 24/7 uptime, automation can help cut costs.
  • Business Hours Usage: If your team primarily operates during specific hours, pausing capacity during off-hours prevents unnecessary spending.
  • Seasonal or Project-Based Needs: Companies with fluctuating workloads based on seasons or projects can optimise expenses by automating capacity scaling.
Continue reading “Microsoft Fabric: Revealing Cost-Saving Results from Automating Pause & Resume Fabric Capacity” →
Azure, Fabric Administrator, Fabric Capacity, Microsoft Fabric, Power BI

Microsoft Fabric: Capacity Cost Management Part 3, Pause Capacity During Christmas with Azure Logic Apps

Microsoft Fabric: Capacity Cost Management Part 3, Pause Capacity During Christmas with Azure Logic Apps

In the first blog post of this series, I explained that we can Pause and Resume a Microsoft Fabric capacity from Azure Portal. In the second blog and its accompanying YouTube video, I showed you how to automate the Pause and Resume actions in Azure LogicApps so the capacity starts at 8:00 AM and stops at 4:00 PM. While I have already mentioned in those posts, it is worthwhile to mention again that these methods only make sense for PAYG (Pay-As-You-Go) capacities and NOT the Reservation capacities. While the method works fine, you may need more fine-tuning.

Managing operational costs becomes crucial for businesses leveraging Microsoft Fabric capacities when the holiday season approaches. This presents a unique challenge of maintaining efficiency while reducing unnecessary expenses, especially during Christmas when business operations might slow down or pause entirely.

In this post and video, I will extend the discussions from my previous blog and demonstrate how to optimise your Azure Logic Apps to manage Microsoft Fabric capacity during the Christmas holidays.

Extending the Logic Apps Workflow

Existing Setup Recap

In earlier discussions, we’ve explored using Azure Logic Apps to manage Fabric capacity effectively from 8:00 AM to 4:00 PM on regular business days and pausing operations afterwards. This setup ensures that we’re not incurring costs when the capacity isn’t needed, particularly from 4:00 PM to 8:00 AM the next morning, and throughout the weekends. I encourage you to check out my previous post for more information. This is how the existing solution looks like in Azure LogicApps:

Automating Microsoft Fabric Capacity with Azure LogicApps
Automating Microsoft Fabric Capacity with Azure LogicApps

Incorporating Holiday Schedules

The key to extending this setup for the Christmas period lies in integrating specific holiday schedules into your existing workflows using Workflow Definition Language which is used in Azure Logic Apps and Microsoft Flow. The following expression determines if the current date (in New Zealand Standard Time) falls within the period from December 25th of the current year to January 2nd of the next year:

and(
    greaterOrEquals(
        int(
            formatDateTime(
                convertFromUtc(
                    utcNow(), 
                    'New Zealand Standard Time'
                ), 
                'yyyyMMdd'
            )
        ), 
        int(
   concat(
    formatDateTime(
     utcNow()
     , 'yyyy'
     )
    , '1225'
    )
   ) 
    ), 
    lessOrEquals(
        int(
            formatDateTime(
                convertFromUtc(
                    utcNow(), 
                    'New Zealand Standard Time'
                ), 
                'yyyyMMdd'
            )
        ), 
        int(
   concat(
    add(
     int(
      formatDateTime(
       utcNow()
       , 'yyyy'
       )
      )
     ,1
     )
    , '0102'
    )
   )
  )
)

The following section explains how the expression works.

Continue reading “Microsoft Fabric: Capacity Cost Management Part 3, Pause Capacity During Christmas with Azure Logic Apps” →
Business Intelligence - BI, Fabric Administrator, Fabric Capacity, Good Practices, Microsoft Fabric, Power BI, Power BI Premium Capacity, Power BI Service

Microsoft Fabric: Overcome Reaching the Maximum Number of Fabric Trial Capacities

Microsoft Fabric Overcome Reaching the Maximum Number of Fabric Trial Capacities

If you are evaluating Microsoft Fabric and do not currently own a Premium Capacity, chances are you’re using Microsoft Fabric Trial Capacities. All Power BI users within an organisation or specific security groups given the rights can opt into Fabric Trial Capacities. Therefore, you may already have several Trial Fabric Capacities in your tenant. Your Fabric Administrators can specifically control who can opt into the Fabric Trial capacities within the Fabric Admin Portal, on the Help and support settings section, and enabling the Users can try Microsoft Fabric paid features setting as shown in the following image:

Enable Users can try Microsoft Fabric paid features for specific security groups via Fabric Admin Portal
Enable Users can try Microsoft Fabric paid features for specific security groups via Fabric Admin Portal

The authorised users can then opt into Fabric Trial by following this process:

  1. Click the Account Manager on the top right corner of the page
  2. Click the Start trial button
  3. Click the Start trial button again
  4. Provide the required details
  5. Click the Extend my free trial button

The following image shows the preceding steps:

Start Fabric Free Trial
Start Fabric Free Trial

As you see, opting into Fabric Trial is simple, unless it isn’t!

There are cases where authorised users cannot start their Fabric Trial because their tenant has already exceeded the limit of available trial capacities. In that case, the users get the following message:

Continue reading “Microsoft Fabric: Overcome Reaching the Maximum Number of Fabric Trial Capacities” →