Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
cbrandt
Regular Visitor

Summarize a Column Without Showing Total

I've looked through the forums and can't seem to find anything like this.  I want to group and summarize items in a column but as the numbers in some rows have nothing to do with numbers in others a total at the end does not make sense.  I'n the included photo the first table has a sum on the "Quantity" Column and "XTD Cost" column and it looks the way I want except for the "Total" on the "Quantity" column.  The second table has a sum only on the "XTD Cost".   Any Help is much appreciated!

Thanks!

PowerBICapture.png

 

2 ACCEPTED SOLUTIONS
erik_tarnvik
Solution Specialist
Solution Specialist

Hi @cbrandt,

I recommend using the HASONEVALUE() function to detect whether you are in a total contaxt in the definition of the measures used in your model.

 

Here is a link to an article that explains the concept:

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

View solution in original post

Hi @cbrandt

Your approach is slightly wrong. You should try ...

 


Measure = IF(HASONEVALUE(Table1[Quantity]),  SUM(Table1[Quantity]), BLANK())

The formula can be read as ... If the total of values of the [Quantity] column of the Table1 is one then sum the quantity. If not then leave it BLANK. 

In the totals there is no filter as in each of the rows of the visual you have placed. So ... it takes on account all of the values. Showing the total is prevented by using HASONEVALUE.

Hope That Helps

Vicente

View solution in original post

3 REPLIES 3
erik_tarnvik
Solution Specialist
Solution Specialist

Hi @cbrandt,

I recommend using the HASONEVALUE() function to detect whether you are in a total contaxt in the definition of the measures used in your model.

 

Here is a link to an article that explains the concept:

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Thanks for the help @erik_tarnvik.  I have tried this method but the results are the same as the original.  Probably a mistake in my formula?   

Measure = IF(HASONEVALUE(Table1[Quantity]), VALUES(Table1[Quantity]), SUM(Table1[Quantity]))

PowerBICapture2.png 

Hi @cbrandt

Your approach is slightly wrong. You should try ...

 


Measure = IF(HASONEVALUE(Table1[Quantity]),  SUM(Table1[Quantity]), BLANK())

The formula can be read as ... If the total of values of the [Quantity] column of the Table1 is one then sum the quantity. If not then leave it BLANK. 

In the totals there is no filter as in each of the rows of the visual you have placed. So ... it takes on account all of the values. Showing the total is prevented by using HASONEVALUE.

Hope That Helps

Vicente

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors