topic Re: Reporting on bundled product orders in DAX Commands and Tips

Reporting on bundled product orders

Anonymous — Mon, 02 Dec 2019 15:54:52 GMT

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

Re: Reporting on bundled product orders

Nathaniel_C — Mon, 02 Dec 2019 17:44:19 GMT

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

Re: Reporting on bundled product orders

Nathaniel_C — Mon, 02 Dec 2019 17:48:28 GMT

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

Re: Reporting on bundled product orders

Nathaniel_C — Mon, 02 Dec 2019 18:05:32 GMT

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

Re: Reporting on bundled product orders

Anonymous — Tue, 03 Dec 2019 09:41:47 GMT

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?

Re: Reporting on bundled product orders

Anonymous — Tue, 03 Dec 2019 12:22:33 GMT

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