Would it be possible to post a packaged workbook with some sample data? There's a method to doing this using the LOOKUP() function, but the Compute Using settings for this table calculation will depend on how your view is set up.
Now that I saw your data, here's one way to go about this that is simpler and doesn't require LOOKUP(). Since the data has one row per measure (CellName), the Price is getting calculated for one row (that also has a Null value for Volume), and Volume for another (that also has a Null value for Price), so when CellName is in the view the Volume*Price calc fails because it's always multiplying one of the two Price or Volume values by Null, which returns Null.
The solution is to turn the Price and Volume into aggregates. The IF/THEN formulas already restrict results to the proper values, adding SUM() around the formulas then causes Tableau to aggregate the results (and ignore the Nulls coming from the IF/THEN statements). The Volume*Price calc then needs the SUM()'s removed, and CellName is taken off the level of detail of the view (i.e. not on the Rows or Columns Shelves, or Marks Card) since it's no longer needed.
See the "revised crosstab" worksheet in the attached for details.
when i drill up to the year (or quarter), it again show's 'Volume*Price' as Total Volume(*Total Price
but i need a solution to get total 'Volume*Price' for the year (quarter) as following:
(january price * january volume) + (feb price*feb volume) +... + (dec price*dec volume)
is it possible?
I apologize, I missed that detail in your first post. To do this, I used the view I posted and created two new calculated fields: One that returns the Month of ReportMonthDate (so we can always have the month in the view, regardless of the Year/Quarter/Month drill-down), the second is a table calc called WS Volume*Price with this formula:
The IF and IIF statements reduce the number of operations to just one per partition. With Month of ReportMonthDate on the Level of Detail Shelf, the Compute Using for the WS Volume*Price is set to Month of ReportMonthDate, so it partitions on the given Year/Quarter/Month and generates the appropriate sum.
Does this work for you?
yes it works.
but when i need these caclulations within group (another kind of Volume and Price) i dont now how to use them in that case. so it was easier for my to reshape my data - move Price to separate column