Unit of measure help!!!

[#1]

I have a table that has products and the number of orders in liters. I have another table that the product name with the conversion between liters and cases. The problem is that one product can have the same number of liters but different number of cases. So the cases is really dependent on which product it is.

Table 1: Order by volume

Product, Orders

A, 10 L

B, 20 L

C, 40 L

D, 80 L

Table 2: Conversion table

Product, Liters, Cases

A, 10 L, 2 Cases

B, 10 L, 3 Cases

C, 10 L, 1 Cases

D, 10 L, 6 cases

I need the ability to be able to toggle between liters and cases. What can I do?

[JonnyPoole]

I created a conversion table by dividing Cases/liters for each product in the conversion table

-----------------

OrdersinLiters:

LOAD Product,

    [Orders (L)] as [Orders]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Conversion:

LOAD Product,

    Cases / Liters as [Product-LiterToCase-Conversion]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

------------------

Then the chart expression for L and Cases is easy:

L:    sum(Orders)

Cases:  sum(Orders * [Product-LiterToCase-Conversion])

[maxgro]

PFA

[#4]

Thanks for the solutions guys.

What if I wanted a  button to switch from liters to cases without putting both cases and liter columns?

[maxgro]

one of  the possible solutions

[JonnyPoole]

one more solution....  (i feel like dueling banjos)

[#7]

These are looking great! One additional question, if I were to have one more unit of measure to toggle between. I need to now be able to move it from liters to barrels or cases

Say,

Table 1: Order by volume

Product, Orders

A, 10 L

B, 20 L

C, 40 L

D, 80 L

Table 2: Conversion table

Product, Liters, Cases, Barrels

A, 10 L, 2 Cases, 0.5 Barrel

B, 10 L, 3 Cases, .75

C, 10 L, 1 Cases, .25 Barrel

D, 10 L, 6 cases, 4.5 Barrel

[JonnyPoole]

Here is an updated version to handle Barrels as well.

[maxgro]

and my qvw  too

[#10]

Either of you know why my liter values are being doubled?