Reporting on bundled product orders

[Anonymous]

Good morning community,

I have an existing report that shows quantity of product sold from an invoice table. We have started selling bundles of products eg 1 x ProductX, 2 x ProductY etc under a different SKU. I need the report to show quantites of the component SKUs and ignore the bundle SKU. Fortunately, for each invoiceID, our invoice table contains seperate records for the bundle sku AND the components BUT it shows 0 quantity for the components. I have created a lookup table listing the Bundle SKU, the component SKU and quantity. 

I can easily create a column to check if the SKU on this record is listed as the SKU of a bundle. Where i am struggling, is figuring out how to identify if the SKU on the invoice record is part of a bundle.

An example of invoice data:

InvoiceID	SKU	Quantity
12345	BundleQA	2
12345	ProductA	0
12345	ProductB	0

An example of the lookup table

Bundle SKU	ComponentSKU	Quantity
BundleQA	ProductA	3
BundleQA	ProductB	2

So what i am aiming to get, is a column - lets call it ReportQty - that will show 0 on the invoice record for BundleQA, 3 for ProductA and 2 for ProductB.

I am thinking i need something like matching SKUs in Invoice that ARE a bundle with a subset filtered table on invoice_sku = bundlelookup_componentSKU

[Nathaniel_C]

Hi @Anonymous ,
Not sure if I understand your question but try this:

Report Quantity =
LOOKUPVALUE ( Lookup[Quantity], Lookup[ComponentSKU], MAX ( InvoiceData[SKU] ) )

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,
Or with a zero:

Report Quantity =
IF (
    LOOKUPVALUE ( Lookup[Quantity], Lookup[ComponentSKU], MAX ( InvoiceData[SKU] ) )
        = BLANK (),
    0,
    LOOKUPVALUE ( Lookup[Quantity], Lookup[ComponentSKU], MAX ( InvoiceData[SKU] ) )
)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel 

[Nathaniel_C]

Hi @Anonymous ,
I was thinking that for each invoice, you may want to know the total of each of the components. So a measure to multiply the number of bundles by each of the components that make up that bundle.

Report Qty =
VAR _curInID =
    MAX ( InvoiceData[InvoiceID] )
VAR _maxQtyBundle =
    CALCULATE (
        MAX ( InvoiceData[Quantity] ),
        ALLEXCEPT ( InvoiceData, InvoiceData[InvoiceID] ),
        InvoiceData[Quantity] <> 0
    )
VAR _total =
    _maxQtyBundle
        * (
            IF (
                LOOKUPVALUE ( Lookup[Quantity], Lookup[ComponentSKU], MAX ( InvoiceData[SKU] ) )
                    = BLANK (),
                0,
                LOOKUPVALUE ( Lookup[Quantity], Lookup[ComponentSKU], MAX ( InvoiceData[SKU] ) )
            )
        )
RETURN
    _total

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
 

[Anonymous]

@Nathaniel_C  thanks for the suggestions. I had (sorta) gotten this far also. Unfortunatley that will not always work. ProductA could be a component of more than 1 bundle sku and each bundle SKU could have a different quantity of ProductA. 

The below shows a snippet of the Bundle SKU table and the 2nd image a count of how many bundles each SKU is a component of.

 

So it needs a second criteria identifying which SKU in the BundleSKU table to return the quantity of ComponentSku. For that criteria it needs to know which SKU, so needs a method to compare the SKUs in the invoice table for that invoice ID for a matching Sku in the BundleSKU table. 

Something like - lookupvalue(BundleSKUTable[Quantity],BundleSKUTable[SKU],Matches(A SKU in InvoiceID),BundleSKUTable[BundleSKU],InvoiceData[SKU])

Can we perform a loop (for/next) within a VAR DAX equation?

[Anonymous · Accepted solution]

@Nathaniel_C 

 Looking at the construct of your formulae and thinking about how to return the bundleSKU value, led me to the following.

I have a calculated column (BundledSKU) that evaluates TRUE/FALSE if the invoice record SKU is a SKU in [Bundled SKU Matrix]. Then I can use that as a criteria in a Calculate(Firstnonblank()) to identify the relevant bundle sku name.

Can this be streamlined or is there a better way?

BundledQty = (LOOKUPVALUE('Bundled SKU Matrix'[Qty],'Bundled SKU Matrix'[SKU],CALCULATE(FIRSTNONBLANK(invoicedetails[SKU],TRUE()),filter(invoicedetails,invoicedetails[BundleSKU]=TRUE() && invoicedetails[_invoiceid_value] = EARLIER(invoicedetails[_invoiceid_value]))),'Bundled SKU Matrix'[BundleSKU],invoicedetails[SKU])+0) * CALCULATE(FIRSTNONBLANK(invoicedetails[Quantity],TRUE()),filter(invoicedetails,invoicedetails[BundleSKU]=TRUE() && invoicedetails[_invoiceid_value] = EARLIER(invoicedetails[_invoiceid_value])))+0