Forum Discussion
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!
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/
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
3 Replies
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]))
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
