SumColumn Twice based on Criteria

Hi All -

I have a cube report where I sum sales figures based on various criteria, however, for some reason when the gross sales amount and the net sales amounts are lumped together and thus far I have been adding a sales type budget to filter between gross and net.

I'm building a report, driven by this cube report, that require a column for gross and net sales shown simultaneously. This is where i am hitting a roadblock as I'm unsure how to do so.

I was thinking of adding two measures, each summing YTD Sales (the combined sales figures) if the Sales type is either "Gross" or "Net".


This is what I have so far: SUM(IFF([Sales Type].[Sales Type]="Gross",[Measures].[YTD Sales],0) and SUM(IFF([Sales Type].[Sales Type]="Net",[Measures].[YTD Sales],0) but I keep on getting an error.

Any help would be greatly appreciated.

you write

...if the Sales type is either "Gross" or "Net"...

this means that values should be added if they meet the either/or criteria but you used

Please Login or Register  to view this content.

which is a logical operator.
there is a SUMIF function you can use (SUMIF(range, criteria, [sum_range])) and you have to add "+" them

Please Login or Register  to view this content.

Thanks for the response gue2013. I don't think this is what I was looking for as far as I know, there are no SUMIF/SUMIFS functions for calculated pivot field.

Also to rephrase I'm not trying to get a combined total between gross and net, actually the opposite. The dataset combines gross+net and I am trying to great a measure that separates gross sales and net sales.

oh, I misunderstood. Therefore it is always good to provide a simple sample file with anonymous data