Table omitting one value from Sum

Hi jimrosser ,

Thank you for reaching out to the Microsoft Fabric Community.

Please find attached a sample Power BI file demonstrating how to include blank values in the total using a custom measure. The file also includes an optional approach to label blanks as <No Location> to make the visual more readable.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Hi jimrosser ,

It looks like you're encountering an issue where Power BI is omitting a blank category from your visual when there are other fields present in the table. This behavior typically occurs because Power BI attempts to optimize visuals by not showing blank values unless explicitly instructed. Since your data does contain a truly blank value for Location, it is being grouped under <Blank> and might be dropped depending on visual-level filters or how the DAX measures are written.

To ensure that the blank category is included in your chart or table, even when other fields are present, you can use a DAX measure that explicitly handles blanks. Here's a DAX pattern that may help:

Sales With Blank Handling :=
CALCULATE(
    SUM(Sales[Sales]),
    REMOVEFILTERS(Sales[Location])
)

Or, if you're creating a measure for display in a matrix or table and want to ensure the <Blank> category is preserved, you might need something more targeted like:

Sales Including Blanks :=
CALCULATE(
    SUM(Sales[Sales]),
    ALL(Sales[Location])
)