Grand Total not summing PowerPivot measure column

[lmtaylor]

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]

Try this:

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

 

[lmtaylor]

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]

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

 

[MD610]

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]

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

 

[MD610]

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<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.</sub>