6 Replies Latest reply on Jul 17, 2012 1:57 AM by Vladimir Rybovalov

# Multipluing two dimensions with one measure

I have one dimesion which has  2 fields.

for example 'Volume' and 'Price'

and one measure for them AMT.

I created 3 calc fields"

1) Volume_measure :

if [dimension]= "Volume" then [AMT]

end

2) Prices_measure:

if [dimension]= "Price" then [AMT]

end

3) Volume*Price:

sum ([Tranport_Volume])*sum([Transport_tarif])/[VAT]

the problem is  - price is different for each month, so i get the correct numbers by months, but for the whole year it results in wrong numbers (it multiplies Total Volume*Total Price).

any suggestions how to resolve this issue?

• ###### 1. Re: Multipluing two dimensions with one measure

Hi,

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.

Jonathan

• ###### 2. Re: Multipluing two dimensions with one measure

see attached

thank you!

• ###### 3. Re: Multipluing two dimensions with one measure

You're welcome!

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.

Cheers,

Jonathan

• ###### 4. Re: Multipluing two dimensions with one measure

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?

• ###### 5. Re: Multipluing two dimensions with one measure

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:

IF FIRST()==0

THEN WINDOW_SUM([Volume*Price],0,IIF(FIRST()==0,LAST(),0))

END

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?

Jonathan

• ###### 6. Re: Multipluing two dimensions with one measure

thank 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