Need Help with UOM Conversion measures

Rose · October 17, 2022, 7:28pm

Hello Power BI Brainiacs!

I need your help with 2 things (PBIX has been uploaded):

UOM conversions are not totaling correctly
Report is taking way too long and sometimes erroring out when rendering table if I choose all parts

Background on Topic 1:

Units of measure can be entered at the will of the user into our system. For example, someone can enter a sales order for 500 Meters, then another person can enter another Sales order for 735 Feet. This can happen with any data set - Backlog, Inventory, Sales, etc. So I had to devise a way to handle the UOM conversions once the data gets to Power BI. But it’s not ONLY “convert everything from feet to meters” or “meters to feet.” I need to give users a UOM slicer so they can control what UOM they want to convert to.

In Power BI, the default view is to see the Qty’s in the origional UOM, but the slicer allows them to select the UOM conversion.

My measures work fine (well, not really…that’s explained in Topic 2). The PROBLEM is that once the UOM’s are converted, the converted quantities do not add up.

Screen shot below shows how the totals are not correct if I choose to convert everything that’s NOT feet to feet.

If I choose to convert everything to Meters, the totals match the original UOM.

Topic 2:

I need help with optimizing the conversion measures. Other than the totaling issue mentioned above, they work IF I select one part. As soon as I clear the Mil Spec filter, the table will not render. I ran the Performance Analyzer, but the Sales Detail table takes many, many minutes to render (if it renders at all). So there is an obvious problem in the conversion measures. I do not know enough about how to read the queries that the Performance Analyzer puts out. Backlog and Inventory do take a little bit of time, but nothing like Sales.

I would greatly appreciate it if someone could run the Performance Analyzer on the attached PBIX and tell me what I’m doing wrong with the conversion measures.

Thank you much!

Rose

Customer Forecast Management with Conversion TEST.pbix (40.6 MB)

jafernandezpuga · October 19, 2022, 11:29pm

Hi @Rose
Thanks you very much for posting your query into the forum.
I see that the Item Master table contains 2,074,547 rows. In the actual data source, if you just use the AEP_ITEM_CODE that are present in the Shipments, Inventory and Backlog tables, would the number of rows in this table be reduced?


Item Master New =

VAR _Item_Shipments = VALUES( Shipments[AEP_ITEM_CODE])

VAR _Item_Inventory = VALUES( Inventory[AEP_ITEM_CODE])

VAR _Item_Backlog = VALUES( Backlog[AEP_ITEM_CODE])

VAR _Union_Items = UNION( _Item_Shipments, _Item_Inventory, _Item_Backlog )

VAR _Result = FILTER( 'Item Master', [AEP_ITEM_CODE] IN _Union_Items )

RETURN _Result

Regards,

Rose · October 20, 2022, 1:24pm

jafernandezpuga,

That’s a very interesting way of paring down the Item Master dataset. The Item Master New takes it from over 2 million rows to roughly 135k. I’m definitely going to use that logic in this and other reports.

Unfortunately, that doesn’t solve my main problem of the table taking too long to render when all parts are selected.

The more I research this, the more I’m thinking that the answer is to create a virtual table that contains Customer Name, Part Number, the converted UOM and the results of the UOM conversion. I also think that would solve my problem of the table not totaling correctly.

If anyone can help me with this, I’d greatly appreciate it!

Thanks,
Rose

jafernandezpuga · October 20, 2022, 2:38pm

Hi @Rose,
It would be convenient to generate the Item Master New table from Power Query and thus reduce its records to only those that are being used in the 3 fact tables.
On the other hand, I have created the following measures in the JAFP Measures folder:

Total Sales Qty_JAFP = SUM( Shipments[TRANSACTION_QTY] )

I have substituted SUMX for SUM, since SUMX being an iterator performs the calculations row by row and is less efficient than the SUM aggregation function.

Converted Sales Qty_JAFP =

VAR Orig_UOM = MAX( Shipments[UNIT_OF_MEASURE] )

VAR Sel_UOM = SELECTEDVALUE( 'UOM Conversion Table'[To Unit], Orig_UOM )

VAR Conv_Rate = CALCULATE(

    VALUES( 'UOM Conversion Table'[Rate] ),

        FILTER(

          'UOM Conversion Table',

          'UOM Conversion Table'[From Unit] = Orig_UOM &&

         'UOM Conversion table'[To Unit] = Sel_UOM

        )

)

VAR ConvSalesQty = [Total Sales Qty_JAFP] * Conv_Rate

VAR _Result = IF(

    ConvSalesQty <> 0,

    ConvSalesQty,

    [Total Sales Qty_JAFP]

)

RETURN

_Result

We create another measure to correct the table totals:

Converted Sales Qty_JAFP Totals =

VAR _VirtualTable =

FILTER(

    ADDCOLUMNS(

        CROSSJOIN(

            VALUES( Customers[CUSTOMER CODE] ),

            VALUES( 'Item Master New'[AEP_ITEM_CODE] )

        ),

        "@Converted", [Converted Sales Qty_JAFP]

    ),

    NOT ISBLANK( Customers[CUSTOMER CODE] ) &&

    [@Converted]

)

VAR _Result = IF(

    ISINSCOPE( Customers[CUSTOMER CODE] ),

    [Converted Sales Qty_JAFP],

    SUMX( _VirtualTable, [@Converted] )

)

RETURN

_Result

In the following link published by Greg Deckler on Linkedin we can vote for Microsoft to fix the problem of calculating totals in tables and matrices.

https://lnkd.in/gzRS5ZJ6

Regards

Customer Forecast Management with Conversion TEST_JAFP.pbix (40.6 MB)

Rose · October 20, 2022, 7:21pm

jafernandezpuga,

I was very hopeful that your new measures were going to solve the problem, but they haven’t.

The table will not render at all UNLESS I select a part number (or a few part numbers). This is a problem because users need to see ALL parts for a customer or group of customers. Even if I filter by a Customer, as soon as I clear the Mil Spec filter, the table takes forever to render eventually resulting in this message:

Capture 55

The sample PBIX file you sent me only seems to work because the data set is small. As soon as I imported the full data sets into your PBIX, it doesn’t work.

Unfortunately, I can’t upload my full file. It’s too big.

jafernandezpuga · October 23, 2022, 11:27am

Hi @Rose,
Have you been able to generate the Item Master New table from Power Query and disable the loading of the Item Master table?

If you could share the real data I could continue to see where we could optimize the response time. I don’t know if you can send it by
WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free?

email: jafernandezpuga@gmail.com

Regards,

EnterpriseDNA · November 8, 2022, 8:25am

Hi @Rose

I’d like to check again with you if the answer @jafernandezpuga provided above helped you solve your inquiry.

If it does, kindly mark the answer as the solution that solved your query.

If not, how far did you get, and what kind of help do you need further?

EnterpriseDNA · November 15, 2022, 12:31pm

Hello @Rose , did the response above help solve your query? It’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Rose · November 15, 2022, 1:37pm

The cause of the issue still remains unresolved. It has to do with the conversion measure and I am still trying to resolve that.

I have tried every suggestion posted and even came up with a few myself, but alas, still no definitive answer.

I would appreciate it if you would keep this issue open for another week. I will update the ticket myself if I cannot resolve this within that time.

Thank you for your support.

Rose

EnterpriseDNA · December 30, 2022, 4:43am

Bumping this post

EnterpriseDNA · January 16, 2023, 12:13pm

Hello @Rose . Due to the length by which this post has been active, we are tagging it as Solved. Thanks to the contributors of this post. For further questions related to this post, please make a new thread. Feel free to reopen this thread if you anyone would like to answer the pending inquiry above.

Rose · May 3, 2023, 7:27pm

Jose,

I tried your measures that “correct the totals” and they work beautifully if the tables contain both Customer Name and Mil Spec parts data (Shipments, Forecast, and Backlog data files all contain Customer data AND Mil Spec data). But it stops working when I’m trying to create a UOM conversion measure for tables that don’t have any customer data - for example Inventory and Open PO’s.

I tried a few different things including removing the Crossjoin and just create a virtual table with the Mil Spec and the interim converted Qty, but it doesn’t work. Here’s the example of the measure you gave me that works for tables that contain both Customer and Mil Spec data:

Converted Sales Qty_JAFP Totals =
VAR _VirtualTable =
FILTER(
ADDCOLUMNS(
CROSSJOIN(
VALUES( Customers[CUSTOMER CODE] ),
VALUES( ‘Item Master New’[AEP_ITEM_CODE] )
),
“@Converted”, [Converted Sales Qty_JAFP]
),
NOT ISBLANK( Customers[CUSTOMER CODE] ) &&
[@Converted]
)
VAR _Result = IF(
ISINSCOPE( Customers[CUSTOMER CODE] ),
[Converted Sales Qty_JAFP],
SUMX( _VirtualTable, [@Converted] )
)

RETURN
_Result

For tables that have no Customer data associated with them, I tried this (but doesn’t work):

Converted Inventory Qty_JAFP Totals =
VAR _VirtualTable =
FILTER(
ADDCOLUMNS(
VALUES( ‘Item Master New’[AEP_ITEM_CODE] )
,
“@Converted”, [Converted Inventory Qty_JAFP]
),
[@Converted]
)
VAR _Result =
SUMX( _VirtualTable, [@Converted] )
RETURN
_Result

I’ve attached the updated PBIX with the example of how Inventory conversions are not working.

Thanks for your help!

Rose

Customer Forecast Management with Conversion TEST_JAFP.pbix (40.6 MB)

jafernandezpuga · May 7, 2023, 10:50am

Hi @Rose,
In the case of the Inventory table we are showing values of Inventory[UNIT_OF_MEASURE] and ‘Item Master New’[AEP_ITEM_CODE].
You can modify the Converted Inventory Qty_JAFP Totals measure to create the virtual table as follows:

Converted Inventory Qty_JAFP Totals = 
VAR _VirtualTable = FILTER(
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES( Inventory[UNIT_OF_MEASURE] ),
            VALUES( 'Item Master New'[AEP_ITEM_CODE] )
        ),
        "@Converted", [Converted Inventory Qty_JAFP]
    ),
    [@Converted]
) 
VAR _Result = IF(
    ISINSCOPE( Inventory[UNIT_OF_MEASURE] ),
    [Converted Inventory Qty_JAFP],
    SUMX( _VirtualTable, [@Converted] )
) 

RETURN
_Result

I have modified the Converted Sales Qty_JAFP Totals measure to work on the Sales Details table.

Converted Sales Qty_JAFP Totals = 
VAR _VirtualTable =
FILTER(
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES( Shipments[UNIT_OF_MEASURE] ),
            VALUES( 'Item Master New'[AEP_ITEM_CODE] )
        ),
        "@Converted", [Converted Sales Qty_JAFP]
    ),
    [@Converted]
) 
VAR _Result = IF(
    ISINSCOPE( Shipments[UNIT_OF_MEASURE] ),
    [Converted Sales Qty_JAFP],
    SUMX( _VirtualTable, [@Converted] )
) 

RETURN
_Result

I have also removed the CUSTOMER CODE Is not Blank filter from both tables.

Regards,

Customer Forecast Management with Conversion TEST_JAFP.pbix (40.6 MB)

Rose · May 9, 2023, 8:32pm

Thank you so much! This worked perfectly for those fact tables!

I’ve been struggling with this for months! Now I can finally publish this with confidence in the data.

Thank you again!

Rose