Count Number of Bundles sold based on Invoice number.

[kpfootball1029]

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]

@kpfootball1029 
Hi mate
if you want i share with you a link to download a pbi file with my solution : https://gofile.io/?c=bUaaru
It works very good
However, the measure it a bit large 

[Anonymous]

Try the measure you've written on really big models.

Question: Do you really have to use imprecise and slower floats to calculate counts? You've got 4 calculate(table)'s in your code. Try to count the number of context transitions as well. Then you'll know that this measure is at least problematic.

And please do not get me wrong. I'm scrutinizing your measure a bit and based on my experience... It's not as good as you'd like it to be. It'll very likely bring the whole calculation to a halt on sufficiently large models.

A fast measure is one that uses the Storage Engine most of the time. Not the Formula Engine. Unfortunately, your formulas force the use of the FE.

Especially using DIVIDE under SUMX is a sure way to invoke CALLBACKS.

Best
D

[Anonymous]

Sorry but not everyone can be so smart as you do
Many people are here to study DAX ( so do I as i am new ) 

[Anonymous]

@Anonymous, I have just given you a scrutiny of your code. I'm not criticizing you - I'm pointing out weaknesses of the code.

I have studied DAX as well but not on forums but by reading books and taking courses, creating models for big companies. On forums you can barely touch the subject. DAX is hard. Please do yourself a favour and do not use this forum as a way to get to know DAX. It's pointless. Learn from the masters of their trade - Alberto Ferrari and Marco Russo.

Best
D

[Anonymous]

Yeap, there is no doubt that i can study more by reading books of that guys 😃
I will do that for sure
Thank you

[Anonymous]

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

Here's your measure... that works OK with negatives as well.

Best

D

[kpfootball1029]

Hello D, I just tried this measure on my dataset and it does not appear to be counting an invoice that has 2 units of each product as 2 bundles. Instead it is counting it as 0 bundles so my count is coming up short. It is entirely possible I made a mistake putting this into Power BI, but I want to check to see if this solution is set up for that scenario as well? Do you have an example Power BI file where this works? Thank you! - Ken

[Anonymous]

Sure I have... This is what the measure returns. If you have a case where you think it does not work... please show it to me and I'll update my file. Thanks.

[kpfootball1029]

I was limited by characters in my last response but here are my screenshots of the code. Along with a mannual calculation of what I need.

Data Set with correct bundle amount at invoice level.

Invoice	COMPONENT	Sum of Units	Bundle Count (Done Manually)
1111	A	1	1
1111	B	1
1111	C	1
1111	I	1
1111	O	1
1111	Y	1
2222	A	1	1
2222	B	1
2222	C	1
2222	I	1
2222	O	2
2222	Y	1
3333	A	1	1
3333	B	1
3333	C	1
3333	I	1
4444	A	1	1
4444	B	1
4444	C	1
4444	I	1
5555	A	1	1
5555	B	1
5555	C	1
5555	I	1
5555	O	1
5555	Y	1
6666	A	1	1
6666	B	1
6666	C	1
6666	I	1
6666	P	1
7777	A	1	1
7777	B	1
7777	C	1
7777	D	1
7777	I	1
7777	X	6
7777	Y	2
8888	A	1	1
8888	B	1
8888	C	1
8888	I	1
8888	P	1
9999	A	2	1
9999	B	1
9999	C	1
9999	D	1
9999	I	1
9999	X	6
9999	Y	2
11110	A	1	1
11110	B	1
11110	C	1
11110	D	1
11110	I	1
11110	X	6
11110	Y	2
12221	A	1	1
12221	B	1
12221	C	1
12221	D	1
12221	I	1
12221	X	6
12221	Y	2
13332	A	1	0
14443	A	1	1
14443	B	1
14443	C	1
14443	I	1
14443	P	1
15554	A	1	0
16665	A	1	1
16665	B	1
16665	C	1
16665	D	1
16665	I	1
16665	X	6
16665	Y	2
17776	A	1	1
17776	B	1
17776	C	1
17776	D	1
17776	I	1
17776	X	6
17776	Y	2
18887	Z	1	0
19998	A	1	1
19998	B	1
19998	C	1
19998	I	1
19998	O	1
19998	Y	1
21109	A	1	1
21109	B	1
21109	C	1
21109	I	1
21109	O	2
21109	Y	1
22220	A	1	1
22220	B	1
22220	C	1
22220	I	1
22220	O	2
22220	Y	1
23331	A	2	2
23331	B	2
23331	C	2
23331	I	2
23331	P	2
24442	A	1	1
24442	B	1
24442	C	1
24442	I	1
24442	P	1
25553	A	2	2
25553	B	2
25553	C	2
25553	I	2
25553	P	2
26664	A	1	1
26664	B	1
26664	C	1
26664	F	3
26664	H	1
26664	I	1
26664	M	2
26664	N	2
27775	A	1	1
27775	B	1
27775	C	1
27775	I	1
27775	P	1
28886	A	1	1
28886	B	1
28886	C	1
28886	I	1
28886	P	1
29997	A	1	1
29997	B	1
29997	C	1
29997	I	1
29997	P	1
31108	A	1	1
31108	B	1
31108	C	1
31108	I	1
31108	P	1
32219	A	1	1
32219	B	1
32219	C	1
32219	I	1
32219	P	1
33330	A	1	1
33330	B	1
33330	C	1
33330	I	1
33330	P	1
34441	A	1	1
34441	B	1
34441	C	1
34441	I	1
34441	P	1
35552	A	1	1
35552	B	1
35552	C	1
35552	I	1
35552	P	1
36663	J	2
36663	K	1
36663	M	1
37774	A	1	1
37774	B	1
37774	C	1
37774	I	1
37774	P	1
38885	A	1	1
38885	B	1
38885	C	1
38885	I	1
38885	P	1
39996	A	1	1
39996	B	1
39996	C	1
39996	I	1
39996	P	1
41107	A	1	1
41107	B	1
41107	C	1
41107	I	1
41107	P	1
42218	A	1	1
42218	B	1
42218	C	1
42218	I	1
42218	P	1
43329	L	1	0
44440	B	1	0
44440	M	1
45551	Q	0	0
46662	Q	0	0
47773	Q	0	0
48884	Q	0	0
49995	A	1	0
5555555	A	-2	-2
5555555	B	-2
5555555	C	-2
Grand Total		248	34

[kpfootball1029]

Hello D, I have replicated the measure with a new dataset that more closely resembles my work data. The count I am getting is 31, but the correct count would be 34. I have included the dataset and screenshots of what I have. I'd send the .pbix file but I do not see an option for me to attach the file. Thank you! - Ken

Data Set

Invoice	COMPONENT	Units
1111	A	1
2222	A	1
3333	A	1
4444	A	1
5555	A	1
6666	A	1
7777	A	1
8888	A	1
9999	X	1
9999	X	1
11110	A	1
12221	A	1
9999	A	1
9999	A	1
13332	A	1
14443	A	1
15554	A	1
16665	A	1
17776	A	1
11110	X	1
11110	X	1
12221	X	1
12221	X	1
7777	X	1
7777	X	1
17776	X	1
17776	X	1
11110	Y	1
7777	Y	1
12221	Y	1
12221	Y	1
9999	Y	1
16665	X	1
16665	X	1
11110	Y	1
9999	X	1
12221	X	1
11110	X	1
12221	X	1
9999	X	1
18887	Z	1
12221	D	1
9999	D	1
16665	D	1
7777	D	1
11110	D	1
17776	D	1
12221	B	1
11110	X	1
2222	Y	1
19998	Y	1
21109	Y	1
5555	Y	1
1111	Y	1
22220	Y	1
23331	A	1
24442	A	1
25553	A	1
25553	A	1
26664	F	1
26664	A	1
27775	B	1
26664	C	1
11110	X	1
12221	X	1
28886	A	1
12221	X	1
11110	X	1
19998	B	1
25553	B	1
23331	B	1
24442	B	1
29997	B	1
28886	B	1
23331	B	1
25553	B	1
21109	B	1
31108	B	1
22220	B	1
32219	B	1
33330	B	1
8888	B	1
34441	A	1
35552	A	1
26664	H	1
2222	B	1
22220	I	1
25553	I	1
26664	I	1
25553	I	1
11110	I	1
28886	I	1
9999	I	1
14443	I	1
29997	I	1
36663	J	1
33330	I	1
12221	I	1
31108	I	1
37774	I	1
17776	I	1
26664	F	1
36663	K	1
16665	I	1
8888	I	1
33330	A	1
31108	A	1
22220	A	1
38885	I	1
39996	I	1
32219	I	1
6666	I	1
1111	I	1
5555	I	1
4444	B	1
3333	B	1
19998	I	1
2222	I	1
37774	A	1
21109	A	1
19998	A	1
23331	A	1
41107	A	1
29997	A	1
27775	A	1
42218	A	1
28886	C	1
14443	C	1
37774	C	1
39996	C	1
16665	X	1
16665	X	1
43329	L	1
26664	M	1
26664	F	1
26664	M	1
16665	B	1
14443	B	1
41107	B	1
37774	B	1
39996	B	1
36663	J	1
26664	N	1
26664	N	1
26664	B	1
5555	B	1
42218	B	1
35552	B	1
34441	B	1
6666	B	1
17776	B	1
1111	B	1
38885	B	1
22220	O	1
5555	O	1
19998	O	1
21109	O	1
2222	O	1
22220	O	1
1111	O	1
21109	O	1
2222	O	1
17776	Y	1
7777	Y	1
7777	X	1
7777	X	1
16665	Y	1
16665	Y	1
17776	Y	1
9999	Y	1
9999	X	1
9999	X	1
11110	B	1
9999	B	1
7777	B	1
16665	C	1
8888	C	1
17776	C	1
32219	C	1
31108	C	1
22220	C	1
33330	C	1
38885	C	1
42218	C	1
27775	C	1
38885	P	1
41107	P	1
29997	P	1
32219	P	1
34441	P	1
14443	P	1
6666	P	1
25553	P	1
37774	P	1
24442	P	1
27775	P	1
42218	P	1
28886	P	1
39996	P	1
31108	P	1
25553	P	1
35552	P	1
23331	P	1
23331	P	1
8888	P	1
33330	P	1
17776	X	1
16665	X	1
17776	X	1
16665	X	1
17776	X	1
17776	X	1
7777	X	1
7777	X	1
44440	M	1
36663	M	1
4444	C	1
3333	C	1
4444	I	1
3333	I	1
45551	Q	1
46662	Q	1
46662	Q	-1
47773	Q	1
47773	Q	-1
45551	Q	-1
48884	Q	-1
48884	Q	1
44440	B	1
34441	C	1
7777	C	1
35552	C	1
6666	C	1
5555	C	1
39996	A	1
49995	A	1
23331	I	1
21109	I	1
7777	I	1
24442	I	1
35552	I	1
42218	I	1
41107	I	1
23331	I	1
34441	I	1
27775	I	1
21109	C	1
23331	C	1
25553	C	1
25553	C	1
29997	C	1
11110	C	1
2222	C	1
24442	C	1
12221	C	1
41107	C	1
23331	C	1
9999	C	1
1111	C	1
19998	C	1
32219	A	1
38885	A	1
5555555	A	-1
5555555	B	-1
5555555	C	-1
5555555	A	-1
5555555	B	-1
5555555	C	-1

Correct Output

Invoice	Bundle Count
1111	1
2222	1
3333	1
4444	1
5555	1
6666	1
7777	1
8888	1
9999	1
11110	1
12221	1
13332	0
14443	1
15554	0
16665	1
17776	1
18887	0
19998	1
21109	1
22220	1
23331	2
24442	1
25553	2
26664	1
27775	1
28886	1
29997	1
31108	1
32219	1
33330	1
34441	1
35552	1
36663	0
37774	1
38885	1
39996	1
41107	1
42218	1
43329	0
44440	0
45551	0
46662	0
47773	0
48884	0
49995	0
5555555	-2
Grand Total	34

[Anonymous]

The assumption in the formula is that you can't have such a case:

5555555 A -1
5555555 B -1
5555555 C -1
5555555 A -1
5555555 B -1
5555555 C -1

The cases should be aggregated there should never be a repeated value of A, B, C,.... Th above should be A -> -2, B -> -2....

Why do you have repeats?

Best
D

[kpfootball1029]

There are repeats because the component is a hiearchy above item number which would be a unique value for each row, creating repeats on the component field. We would likely need a step that groups the rows by component first to eliminate those then?

[Anonymous]

Yes, the formula should be adjusted to account for such cases. I'll do it in a moment.

Best
D

[Anonymous · Accepted solution]

# 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

[Fxelixe]

what if the bundle is valid if they should buy A B C minimum 2 units on each products?

[Greg_Deckler]

Likely better ways to do this but this seems to work. PBIX attached.

Measure = 
    VAR __Table = FILTER('Table',[Units]>0)
    VAR __Table1 =
        SUMMARIZE(
            'Table',
            [Invoice],
            "A",SUMX(FILTER(__Table,[Invoice]=EARLIER('Table'[Invoice]) && [Product Component] = "A"),[Units]),
            "B",SUMX(FILTER(__Table,[Invoice]=EARLIER('Table'[Invoice]) && [Product Component] = "B"),[Units]),
            "C",SUMX(FILTER(__Table,[Invoice]=EARLIER('Table'[Invoice]) && [Product Component] = "C"),[Units])
        )
RETURN
    COUNTROWS(FILTER(__Table1,[A] > 0 && [B] > 0 && [C] > 0))

[kpfootball1029]

Hi Greg! I appreciate your reply! I took a look at the output and put it into a table with the Invoice and Measure and it appears to only return Invoices 111 and 222 with values of 1 bundle each. Ideally, it would return all invoices, with a value of the number of bundles something like below. - Thank you, Ken

Invoice	Bundles (Measure)
111	1
222	2
333	0
444	0
555	-1
Total	2

[Anonymous]

Hello

take a look at my solution please. hope it will help you

[Anonymous]

@Anonymous, your measure breaks when there are negative numbers in an invoice of different value. For instance, A -> -1, B -> -3, C -> -2. Your formula will return -3 and should -1.

Best
D

[Anonymous]

@AnonymousHello

Coud you please take a look, if you have some time on my solution. It seems to work
PS : in order to fix a wrong total in my first table i added an additional measure in my second table

Thanks