Add Units from Unit of Measure Table

[Anonymous]

I have a report I'm trying to create where there are mixed units of measurement based on which product is shipped.

Some of our products have a measurement of Gallons, some Lbs, and some Each. I have a table relating product, size, unit of measure, and conversion from each shipped to the associated unit of measure. I created a calculated column on the sales table "Shipped UOM = [Shipped Qty] * RELATED('Product UOM'[UOM per Piece])". I would like graphics to display 50 Gallons or 2 Lbs instead of 50 or 2 (depending on the product measurement unit).

I've tried changing the format of the column to "###,RELATED('Product UOM'[UOM])", but this only displays "RELATED('Product UOM'[UOM])" for every value instead of the quantity with units.

I've also tried using the FORMAT dax command, but this makes the data in the format of text which makes it difficult to sum gallons / lbs by product or month.

I saw it's possible to use units of measure of thousands, millions, etc. but I can't find how to add units to a matrix or chart. Adding the units as a tooltip gives me options of first UOM or count of UOM, neither of which is useful in this case.

Subset of Product UOM Table

Subset of Sales Table

Relationships in the Model

[lbendlin]

Read about Calculation Groups and the formatting options there.