# Grand Total not summing PowerPivot measure column



## lmtaylor (Mar 21, 2013)

I need a grand total that sums the column.  I have an if statement in my measure and the Grand Total is showing up as blank rather than summing the column.  I know that sumx will usually work but I'm not sure where to put it here.
Here is the formula:  =if(countrows(values(Sub))=1,if(values(Sub)=01,COGS[3 Month Avg COGS]*3,COGS[3 Month Avg COGS]*2))


----------



## MD610 (Mar 21, 2013)

Try this:

=SUMX(VALUES(Sub), IF(VALUES(Sub)=1,COGS[3 Month Avg COGS]*3,COGS[3 Month Avg COGS]*2))


----------



## lmtaylor (Mar 21, 2013)

This gives an error message-  "Calculation error in measure 'COGS'Target Inv Level]: A table of mulitple values was supplied where a single value was expeceted.  
I need the if statement to be 01 because the Sub is text and defined as 01 not 1.


----------



## MD610 (Mar 21, 2013)

Try replacing the 2nd argument of the SUMX with your original measure.


----------



## MD610 (Mar 21, 2013)

Sorry. I don't think you need the VALUES() inside the IF() with a SUMX.


If removing 2nd VALUES() in mine doesn't work, then try replacing the 2nd argument of the SUMX with your original measure.


----------



## lmtaylor (Mar 25, 2013)

Hm... still gives an error message but I found a workaround.  Thanks for your help!


----------



## MD610 (Mar 28, 2013)

Sorry, maybe I wasn't clear when I said remove the second VALUES().  I meant leave the column reference but just get ride of the VALUES() around it:

=SUMX(VALUES(Sub), IF(Sub=1,COGS[3 Month Avg COGS]*3,COGS[3 Month Avg COGS]*2))

This should work without errors, but as long as you have a workaround that works then you are good to go.


----------

