Table omitting one value from Sum

[jimrosser]

Hi my chart is omitting a value for a blank category I show it as <blank> but in my data it truly is blank.

It sums properly when it is the only variable in the table other than that PBI just omits the value.

Location	Sales	Comment
North	100
East	150
South	200
West	250
<Blank>	300	Omitted
Total	700
Location	Sales	Comment
<Blank>	300	Blank is field is actually 0
Total	300

[v-tsaipranay · Accepted solution]

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.

[v-tsaipranay]

Hi @jimrosser   ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rohit1991 · Accepted solution]

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])
)

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[v-tsaipranay · Accepted solution]

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.

[ryan_mayu]

@jimrosser 

you can try this to create a measure

Measure = if(ISFILTERED('Table'[Location]),sum('Table'[Sales]), sumx(FILTER('Table','Table'[Location]<>""),'Table'[Sales]))

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[jimrosser]

My total in Power Bi should be 1,000 for all 5 rows or data.  However it's excluding the Row with no row label value of 300 from the Total and only showing the total as 700.  If I filter out all the row values with a row label then it will show the total as 300.