cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count Number of Bundles sold based on Invoice number.

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````# 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 =
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

28 REPLIES 28
Anonymous
Not applicable

@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
Not applicable
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
Not applicable

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
Not applicable
@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
Not applicable

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
Not applicable

``````# 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

Frequent Visitor

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
Not applicable

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.

Frequent Visitor

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
Frequent Visitor

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
Not applicable
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
Frequent Visitor

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
Not applicable
Yes, the formula should be adjusted to account for such cases. I'll do it in a moment.

Best
D
Anonymous
Not applicable

``````# 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 =
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

Frequent Visitor

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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
Not applicable

Hello

Anonymous
Not applicable
@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
Not applicable

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors