topic Re: Count Number of Bundles sold based on Invoice number. in DAX Commands and Tips

Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Tue, 25 Feb 2020 21:45:47 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

Greg_Deckler — Tue, 25 Feb 2020 21:53:32 GMT

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))

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 02:12:38 GMT

Hi Greg.

Please do not use EARLIER. Use variables instead. EARLIER only obscures things and is completely redundant in the language with the introduction of variables.

Second, SUMMARIZE is a VERY dangerous function. Please do not do any calculations inside it. Instead, please use the combination SUMMARIZE/ADDCOLUMNS. I thought you did know you should NEVER use SUMMARIZE with calculations...

Best
D

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 13:51:51 GMT

I appreciate the reply D, do you have an example of what you would do? I am pretty new to DAX so not sure how I would edit the above Measure based on your input. Thank you! - Ken

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 13:59:36 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 14:32:20 GMT

// Columns must always be qualified // with the table name. Measures // should never be qualified with // table name. The exception is // when you refer to columns added // to a table via a function. Measure = VAR __Table = FILTER ( 'Table', 'Table'[Units] > 0 ) VAR __Table1 = ADDCOLUMNS( VALUES( 'Table'[Invoice] ), "A", var __currentInv = 'Table'[Invoice] return SUMX ( FILTER ( __Table, and( 'Table'[Invoice] = __currentInv, 'Table'[Product Component] = "A" ) ), 'Table'[Units] ), "B", var __currentInv = 'Table'[Invoice] return SUMX ( FILTER ( __Table, and( 'Table'[Invoice] = __currentInv, 'Table'[Product Component] = "B" ) ), 'Table'[Units] ), "C", var __currentInv = 'Table'[Invoice] return SUMX ( FILTER ( __Table, and( 'Table'[Invoice] = __currentInv, 'Table'[Product Component] = "C" ) ), 'Table'[Units] ) ) RETURN COUNTROWS ( FILTER ( __Table1, true() && [A] > 0 && [B] > 0 && [C] > 0 ) )

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 14:53:31 GMT

Hi D, thank you for the response! Similar to Greg's solution when I place this measure in a table with the Invoice numbers I only see Invoices 111 and 222 with the measure flagging 1 bundle per invoice. Ideally the output of the measure would show the below output. Is it possible to adjust the DAX measure to get this output? Thank you! - Ken

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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 15:04:23 GMT

Hello

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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 15:24:05 GMT

Well, of course, it's similar because it's the same. I just translated what Greg produced into a more digestible and safer version. But the output must be the same.

Has Anonymous got a better and correct solution? If he has, then let me know. If he has not, then I'll look into this a bit more.

Best
D

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 15:44:52 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 16:14:58 GMT

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

Best

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 18:06:02 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 18:29:27 GMT

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.

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 19:20:23 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 20:31:02 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Wed, 26 Feb 2020 21:08:21 GMT

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

Re: Count Number of Bundles sold based on Invoice number.

kpfootball1029 — Wed, 26 Feb 2020 21:15:40 GMT

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?

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Thu, 27 Feb 2020 08:41:44 GMT

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

Best
D

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Thu, 27 Feb 2020 09:24:50 GMT

# 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

Re: Count Number of Bundles sold based on Invoice number.

Anonymous — Thu, 27 Feb 2020 09:34:09 GMT

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