Unit of Measure (UOM) Measure not always working

[Roseventura]

I'm having a hard time figuring out why my unit of measure measure works sometimes and sometimes not.

I need to convert Meters to Feet and vice versa, but before doing that I was checking to make sure the Converted Sales Qty matched the original qty because my conversion (from Meters to Feet) was off. 

Below shows the Sales Qty and UNIT_OF_MEASURE as it was originally recorded in our system.  The columns "Converted Sales Qty" and Converted Sales UOM are the results of my measure (see below).  The "Delta" column shows how several Quantities somehow get calculated even though it should be a 1-to-1 match (Meters to Meters).

Year-Mo	Cust Code	AEP Item Code	Sales Qty	UNIT_OF_MEASURE	Converted Sales Qty	Converted Sales UOM	Delta
2022-04	FRGK	ABEN2714013B002F	10,108	METERS	13,768	METERS	3,660
2022-03	FRGK	ABS1503KD24	800	METERS	3,200	METERS	2,400
2022-03	FRGK	44A0111-22-0(100)	300	METERS	500	METERS	200
2022-03	FRGK	44A0111-24-9(100)	200	METERS	400	METERS	200
2022-06	FRGK	SD32244PS6	200	METERS	400	METERS	200
2022-01	FRGK	SDMX100Q-24	711	METERS	711	METERS	-
2022-01	FRGK	RZ75941  1929	601	METERS	601	METERS	-
2022-01	FRGK	TE75911  1936	200	METERS	200	METERS	-
2022-01	FRGK	CBRAY9030	100	METERS	100	METERS	-
2022-01	FRGK	BABMS13-48T03C03G012	60	METERS	60	METERS	-
2022-01	FRGK	RZP6144  1934CDAA	45	METERS	45	METERS	-
2022-01	FRGK	BA 6081  1932	30	METERS	30	METERS	-
2022-01	FRGK	ST053R5X1    RNF       0	24	METERS	24	METERS	-
2022-02	FRGK	BA48101  1936	7,373	METERS	7,373	METERS	-
2022-02	FRGK	SDMX100Q-24	606	METERS	606	METERS	-
2022-02	FRGK	BA48273  1932	120	METERS	120	METERS	-
2022-02	FRGK	BA 4831  1930	50	METERS	50	METERS	-
2022-03	FRGK	BA 4831  1930	1,097	METERS	1,097	METERS	-
2022-03	FRGK	SDMX100Q-24	712	METERS	712	METERS	-
2022-03	FRGK	ST053R5X  316RNF       0	300	METERS	300	METERS	-
2022-03	FRGK	44A0111-24-0(100)	200	METERS	200	METERS	-
2022-03	FRGK	ST053R5X  14 RNF       0	128	METERS	128	METERS	-
2022-03	FRGK	ST053R5X  18 RNF       0	100	METERS	100	METERS	-
2022-03	FRGK	RZ75943  1936	61	METERS	61	METERS	-
2022-03	FRGK	BMS13-58T09C01G020	50	METERS	50	METERS	-

Here is my conversion table:

Index	From Unit	To Unit	Rate
1	EACH	EACH	1
2	FEET	CENTIMETER	30.48
3	FEET	FEET	1
4	FEET	INCHES	12
5	FEET	METERS	0.3048
6	FEET	MILLIMETER	304.8
7	KILOMETER	KILOMETER	1
8	KILOMETER	METERS	1000
9	KILOMETER	MILE	0.62137
10	KILOMETER	YARD	1093.6133
11	METERS	CENTIMETER	100
12	METERS	FEET	3.28084
13	METERS	INCHES	39.37008
14	METERS	METERS	1
15	METERS	YARD	1.09361

As you can see the Meters to Meters is 1, I still get a converted number that is higher than the Original Sales Qty.

Here are my measures:

Converted Sales Qty =

VAR sls_tem_tbl= ADDCOLUMNS(

ADDCOLUMNS(Shipments,"rate_",

MAXX(

RELATEDTABLE('UOM Conversion Table'),

'UOM Conversion Table'[Rate])),

"conv_UOM",[Sales Qty] * [rate_])

RETURN

SUMX(sls_tem_tbl,[conv_UOM])

Converted Sales UOM =

VAR COV_RATE = MAXX(

FILTER(

RELATEDTABLE('UOM Conversion Table'),'UOM Conversion Table'[From Unit]=MAX(Shipments[UNIT_OF_MEASURE])),'UOM Conversion Table'[Rate])

VAR SelectUOM = SELECTEDVALUE('UOM Conversion Table'[To Unit], BLANK() )

VAR ConvSalesQty = [Sales Qty] * COV_RATE

RETURN

if(ConvSalesQty <> 0, MAXX ( 'UOM Conversion Table' , SelectUOM ) , BLANK())

[HotChilli]

What about removing the relationship between UOM and UOM conversion and getting the rate with

LOOKUPVALUE('UOM Conversion'[Rate], 'UOM Conversion'[From Unit], Shipments[Unit of Measure],

'UOM Conversion'[To Unit], yyyy)

where yyyy is the slicer value?

Something like that, I haven't tested it.

This can be used in Converted Sales Qty meaure.

---

I don't really get what Converted Sales UOM is doing apart from returning the value in the slicer (in a complicated way) so maybe you can simplify this too)

[Roseventura]

HotChilli,

I tried your suggestion, but it brought the processing time of the measure to a crawl.  I will have to play with it a little more to see if I can figure out why this is happening. 

Please leave this open.  I will repost after more troubleshooting and let you know if your suggestion is the solution. 

Thank you.

[Roseventura]

HotChilli,

I got my measure to work by incorporating your suggestion.  My only problem now is that the total is not calculating correctly.

Here is my new measure:

 Converted Sales Qty =
    VAR Orig_UOM =
        IF (
            [Total Sales Qty] > 0,
            MAXX (
                PBI_AEP_Shipments_Multi_wCustPart,
                PBI_AEP_Shipments_Multi_wCustPart[UNIT_OF_MEASURE]
            )
        )
    VAR Sel_UOM =
        IF (
            [Total Sales Qty] > 0,
            SELECTEDVALUE (
                'UOM Conversion Table'[To Unit],
                Orig_UOM
            )
        )
    VAR Conv_Rate =
        LOOKUPVALUE (
            'UOM Conversion Table'[Rate],
            'UOM Conversion Table'[From Unit], Orig_UOM,
            'UOM Conversion table'[To Unit], Sel_UOM
        )
    RETURN
        [Total Sales Qty] * Conv_Rate

Here are the results of the measure:

The first 2 columns represent the original Sales Qty and associated original UOM.  The 3 & 4th columns are the converted values (if necessary) and UOM chosen by the user.  You can see that the bottom row was already in METERS, so it didn't convert it.  That's good.

The conversions are correct, but the total is wrong.

I thought that maybe I had to put this in a virtual table, but when I tried that, my converted numberes were wrong again, meaning I was back to my original problem.

How do I handle adding up the converted quantities?

Thanks for all your help.

[HotChilli]

"Unit of Measure table" - is this the Conversions table?

"on the field Unit of Measure" - just to be sure, which field in the Conversions table? (and is the relationship filtered in one direction?)

----

And the measure 'Converted Sales UOM', does the variable "SelectUOM = SELECTEDVALUE('UOM Conversion Table'[To Unit] " get the value from a slicer? Are there any circumstances where this measure should not return the value in the variable?

-------

I think I would be leaning towards a disconnected table and a LOOKUPVALUE solution.

I'm not available to look at this for a few hours but if you clarify the above points, I will reply later.

[Roseventura]

I should clarify:  There is an indirect relationship between Shipments and UOM Conversion tables.  There's another table called UOM which stores a unique list of ALL the UOM's my company uses. (maybe I don't need this?)

1. Yes, the "Unit of Measure table" is a conversion table which has a many-to-one relationship with the UOM table.
2. The one-to-many relationship is between the UOM table and the Shipments table, not between the UOM Conversion table and Shipments.
3. Yes, the measure 'Converted Sales UOM', does get it's variable "SelectUOM = SELECTEDVALUE('UOM Conversion Table'[To Unit] " from a slicer.  End users want this control.

This shows the relationships between UOM Conversion, UOM and Shipments:

Thank you for your help!

[Roseventura]

There's a 1-to-many relationship between the Unit of Measure table and the Shipments table on the field Unit of Measure.  The reason I had to do it this "complicated" way is because this dashboard serves people in Europe and North America and everyone wants to decide which unit of measure they want to see on a part.  And things get complicated because orders can be placed in whichever UOM they want.  So I can have one part in our system that has been sold using more than one UOM and it's stored on our system that way.

Example:     Order # 12345 for part ABCD 500 meters    (sold in Europe)

                    Order # 67890 for Part ABCD 350 feet      (sold in North America)

If there is a simpler way to do this I'm all ears!

[HotChilli]

This seems a bit overcomplicated.  What's the relationship between the tables?