Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
@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
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 )
Yeap, there is no doubt that i can study more by reading books of that guys 😃
I will do that for sure
Thank you
# 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
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
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.
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 |
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 |
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?
# 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
what if the bundle is valid if they should buy A B C minimum 2 units on each products?
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))
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 |
Hello
take a look at my solution please. hope it will help you
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |