Category: Business Intelligence – BI

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, Azure SQL Database, Business Intelligence - BI, Fabric Administrator, Fabric Capacity, Microsoft Fabric, Power BI, Power BI Governance, Power BI Service, Purview

Sharing Power BI Reports with External Users – Part 3: Sensitivity Labels, Encryption, and Secure Sharing

Sharing Power BI Reports with External Users the Right Way, Part 3: Sensitivity Labels, Encryption, and Secure Sharing

In Part two of this series, we walked through how to configure your Microsoft Fabric environment to securely share Power BI reports with external users across Microsoft 365 tenants. We covered licensing requirements, admin portal settings, how to invite guest users, and how to share reports directly with them.

Now, in the third and final part of this blog series, we focus on two important areas that are often overlooked:

  • What happens when Microsoft Purview sensitivity labels are applied to a report
  • How to refine admin portal settings to better control guest users’ access to Fabric

This series was originally created to support a YouTube video I published in April 2025. The topic turned out to be too broad to explain well in one blog, so I decided to split it into three parts.

Here is the complete series:

  • Part 1: Understanding the Problem and Core Concepts
    This post explains why external sharing can be tricky, the key requirements to get it working, important terminology, user roles, and how the whole process fits together.
  • Part 2: Hands-On Guide to Setup and Sharing
    A step-by-step walkthrough of how to share reports across tenants, covering licensing, admin portal settings, inviting guest users, and how report access looks from the guest’s side.
  • Part 3: Sensitivity Labels, Encryption, and Secure Sharing (this blog)

In this last part, we will look at what happens when Microsoft Purview sensitivity labels are applied, including access control, and will also discuss key admin settings you may need to adjust for more secure collaboration.

If you like to listen to the content on the go, here is the AI generated podcast explaining everything about this blog 👇.

If you are someone who prefers video over reading, you can watch the full walkthrough here 👇.

Let’s now get into the final piece of this guide.

Sensitivity Labels in Microsoft Fabric

Microsoft Purview sensitivity labels are part of a broader Purview Information Protection framework. These labels are not exclusive to Microsoft Fabric or Power BI. They are designed to be consistently applied across various Microsoft services, including but not limited to Outlook, Word, Excel, SharePoint, and Azure SQL DB. This ensures that data is classified and protected uniformly, regardless of where it is created, stored, or shared. In the context of Power BI, when you apply a sensitivity label to a report, it adds classification metadata and, if configured, applies protection such as encryption and access restrictions. These protections travel with the content. For example, if a report is exported to PDF or PowerPoint, and the label has encryption enabled, that exported file will also be encrypted. So only the users who are authorised to view the content will be able to open it, even outside of the Power BI service. This means your data remains secure not only inside your tenant but also when it moves across users, devices, and even organisations.

What Happens When You Share Encrypted Reports?

Let’s walk through an example.

You share a Power BI report with a guest user. This report has a label applied that encrypts its content. Here is what the guest user can and cannot do:

Continue reading “Sharing Power BI Reports with External Users – Part 3: Sensitivity Labels, Encryption, and Secure Sharing” →
Data Visualisation, Fabric Administrator, Fabric Capacity, Microsoft Fabric, Power BI, Power BI Desktop, Power BI Service

Microsoft Fabric: Unlocking the Secrets to Mastering Shared Semantic Models – Part 2 – Implementation

This blog series complements a YouTube tutorial I published earlier this month, where I quickly covered the scenario and implementation of shared semantic models in Microsoft Fabric. However, I realised this topic demands a more detailed explanation for those who need a deeper understanding of the processes and considerations involved in one of the most common enterprise-grade BI scenarios.

In organisations with strong security and governance requirements, implementing shared semantic models is vital to ensure seamless and secure access to data. These organisations often split roles across various teams responsible for productionising analytics solutions. Typically, they have strict Row-Level Security (RLS) and Object-Level Security (OLS) implemented in their semantic models. The goal is to enable two key groups within the organisation:

  • Report Writers: They must access the semantic models securely. This means having sufficient permissions to create reports while ensuring access is restricted to only the relevant objects and data.
  • End-Users: They need access to trustworthy and relevant information without dealing with underlying complexities. All the heavy lifting should be managed behind the scenes.

The first blog laid the groundwork by covering all the essential core concepts necessary for successfully implementing this scenario. It also provided a clear explanation of the roles involved in the process.

Blog Series Overview

Initially, I planned to cover everything in one post. However, the scope turned out to be too large, so I split it into two parts to ensure clarity and avoid overwhelming readers. Here’s what the series includes:

By the end of this blog, you will apply the understanding from the previous post to a real-world scenario, managing secure access to shared semantic models in Microsoft Fabric, and implement the solution step-by-step.

If you prefer a video format, check out the tutorial on YouTube:

For those who enjoy diving into the details, let’s get started!

Continue reading “Microsoft Fabric: Unlocking the Secrets to Mastering Shared Semantic Models – Part 2 – Implementation” →
Azure, Business Intelligence - BI, Fabric Administrator, Fabric Capacity, Logic Apps, Microsoft Fabric, Power BI

Microsoft Fabric: Resolving Capacity Admin Permission Issues in Automate Capacity Scaling with Azure LogicApps

Resolving Capacity Admin Permission Issues in Automate Capacity Scaling with Azure LogicApps

A while back, I published a blogpost explaining how to use Azure LogicApps to automate scaling Microsoft Fabric F capacities under the PAYG (Pay-As-You-Go) licensing option. Some of my followers reported an issue with their Capacity Admin settings when running the LogicApp solution. The issue was that their capacity admins disappeared after they had run the LogicApps to upscale or downscale the capacity. After some investigation, I found out what the problem was. At the same time, some of my other followers suggested a fix which involved hardcoding the admins into the solution. While this would work in some cases, it is not a practical solution in the long run, as the admin settings may evolve over time. This makes the solution hard to maintain and unreliable. Back then, I suggested using the APIs and an HTTP action in a new LogicApps solution. This blog is the continuation of the previous blog and a quick and easy fix that ensures the automation runs smoothly with minimal to no manual work or maintenance afterwards. I have also published a tutorial video on YouTube explaining the process from the beginning (which was already covered in my previous blog, so I do not explain it here again) which you can watch here:

A Reminder of the Previous Solution

I suggest you read my blog about automating Fabric capacity scaling with Azure LogicApps as it provides a step-by-step guide to implement the solution. But if you have already implemented, or you are just after the fix, jump to the next section. The following image shows how the original solution works:

Automate Scaling Microsoft Fabric F Capacities with Azure LogicApps
Automate Scaling Microsoft Fabric F Capacities with Azure LogicApps

Here is a quick explanation of how it works:

  1. The Trigger runs the workflow automatically every hour.
  2. The Read a Resource, which is an Azure Resource Manager operation, reveals information about a resource that, in our implementation, is a Microsoft Fabric Capacity.
  3. A condition to check the Status of the capacity. If the capacity is Paused (the condition is true), then do nothing. This is needed as this method only works when the capacity is Active.
  4. If the capacity is Active (the condition is false), then check the current time to see if it is between 2pm and 4pm. This is the timeframe for which we want to upscale the capacity.
  5. If the condition is True, then upscale the capacity to F8 using another ARM operation: Create or update a resource.
  6. If the condition is False, then set the capacity SKU to F2.

The solution works fine if you do not have any Capacity Admin settings either on Azure Portal or on Admin Portal on Microsoft Fabric. But in many cases, we indeed have capacity admins. Let’s see what the issue is.

The Problem

The issue arises when we add some capacity administrators; that are wiped after running the above solution in its current implementation. The following image shows the Capacity Admin settings on both portals:

Fabric Capacity Admins on Azure Portal and Fabric Admin Portal
Fabric Capacity Admins on Azure Portal and Fabric Admin Portal

The reason if that the Create or update a resource also updates the properties of the resource with the ones we define in LogicApps. Therefore, if we do not add any capacity admins, we literally empty the existing capacity admins. Let’s run the solution again to understand why this is happening. The following image shows my capacity admins are wiped out after running my LogicApp workflow:

Capacity Admins deleted after running LogicApps
Capacity Admins deleted after running LogicApps

The issue is also demonstrated in the tutorial video on YouTube:

Continue reading “Microsoft Fabric: Resolving Capacity Admin Permission Issues in Automate Capacity Scaling with Azure LogicApps” →