Summarize a Column Without Showing Total

[cbrandt]

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!

[erik_tarnvik · Accepted solution]

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/

[erik_tarnvik · Accepted solution]

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/

[cbrandt]

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

 

[vcastello · Accepted solution]

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