Solved: Count Number of Bundles sold based on Invoice numb... - Page 3

kpfootball1029 · ‎02-25-2020

Hello, I am trying to setup a measure in Power BI that counts the number of a specific bundle that is sold at my company. I have an invoice data table along with a product table and I want to count how many times we sold the combination of "A, B, and C" Product Components on each unique invoice. In the below table the correct answer would be 2 because we have 3 positive occurrences of the bundle and 1 negative occurrence indicating a returned bundle. There are also situations where there may be 2 bundles sold on one invoice, or multiple of one item but you can only classify 1 "bundle" because the other items only have 1 unit each. Please let me know if you need more information to assist with this since I am relatively new to DAX. I greatly appreciate any assistance.

Invoice	Product Component	Units
111	A	1
111	B	1
111	C	1
111	D	1
222	A	2
222	B	3
222	C	2
333	A	1
333	C	1
333	X	1
333	V	1
444	B	1
444	A	1
444	V	1
555	A	-1
555	B	-1
555	C	-1

Anonymous · ‎02-27-2020

# Bundles = 
SUMX(
    VALUES( InvoiceDetails[Invoice] ),
    var __invoiceDetails =
        CALCULATETABLE(
            InvoiceDetails,
            ALLEXCEPT( InvoiceDetails, InvoiceDetails[Invoice] )
        )
    var __onlyABCLines =
        FILTER(
            __invoiceDetails,
            InvoiceDetails[Component] in {"A", "B", "C"}
        )
    var __ABCLinesAggregated =
    	addcolumns(
	    	summarize(
	    		__onlyABCLines,
	    		InvoiceDetails[Component]
	    	),
	    	"UnitsSum",
	    		var __component = InvoiceDetails[Component]
	    		var __invWithComponent =
	    			filter(
	    				__onlyABCLines,
	    				InvoiceDetails[Component] = __component
	    			)
	    		return
	    			sumx(
	    				__invWithComponent,
	    				InvoiceDetails[Units]
	    			)
	    )
    var __abcPresent = COUNTROWS( __ABCLinesAggregated ) = 3
    var __min = MINX( __ABCLinesAggregated, [UnitsSum] )
    var __max = MAXX( __ABCLinesAggregated, [UnitsSum] )
    return
        if( __abcPresent,
            if( __min > 0, __min, __max ),
            0
        )
)

Please try the above. It should now work....

Best

D

View solution in original post

Count Number of Bundles sold based on Invoice number.

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024