Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kpfootball1029
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.

 

 

InvoiceProduct ComponentUnits
111A1
111B1
111C1
111D1
222A2
222B3
222C2
333A1
333C1
333X1
333V1
444B1
444A1
444V1
555A-1
555B-1
555C-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 =
    	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

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

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.

 

Bundles Calc.PNG

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.

 

image.png

 

image.png

 

Data Set with correct bundle amount at invoice level.

 

InvoiceCOMPONENTSum of UnitsBundle Count (Done Manually)
1111A11
1111B1 
1111C1 
1111I1 
1111O1 
1111Y1 
2222A11
2222B1 
2222C1 
2222I1 
2222O2 
2222Y1 
3333A11
3333B1 
3333C1 
3333I1 
4444A11
4444B1 
4444C1 
4444I1 
5555A11
5555B1 
5555C1 
5555I1 
5555O1 
5555Y1 
6666A11
6666B1 
6666C1 
6666I1 
6666P1 
7777A11
7777B1 
7777C1 
7777D1 
7777I1 
7777X6 
7777Y2 
8888A11
8888B1 
8888C1 
8888I1 
8888P1 
9999A21
9999B1 
9999C1 
9999D1 
9999I1 
9999X6 
9999Y2 
11110A11
11110B1 
11110C1 
11110D1 
11110I1 
11110X6 
11110Y2 
12221A11
12221B1 
12221C1 
12221D1 
12221I1 
12221X6 
12221Y2 
13332A10
14443A11
14443B1 
14443C1 
14443I1 
14443P1 
15554A10
16665A11
16665B1 
16665C1 
16665D1 
16665I1 
16665X6 
16665Y2 
17776A11
17776B1 
17776C1 
17776D1 
17776I1 
17776X6 
17776Y2 
18887Z10
19998A11
19998B1 
19998C1 
19998I1 
19998O1 
19998Y1 
21109A11
21109B1 
21109C1 
21109I1 
21109O2 
21109Y1 
22220A11
22220B1 
22220C1 
22220I1 
22220O2 
22220Y1 
23331A22
23331B2 
23331C2 
23331I2 
23331P2 
24442A11
24442B1 
24442C1 
24442I1 
24442P1 
25553A22
25553B2 
25553C2 
25553I2 
25553P2 
26664A11
26664B1 
26664C1 
26664F3 
26664H1 
26664I1 
26664M2 
26664N2 
27775A11
27775B1 
27775C1 
27775I1 
27775P1 
28886A11
28886B1 
28886C1 
28886I1 
28886P1 
29997A11
29997B1 
29997C1 
29997I1 
29997P1 
31108A11
31108B1 
31108C1 
31108I1 
31108P1 
32219A11
32219B1 
32219C1 
32219I1 
32219P1 
33330A11
33330B1 
33330C1 
33330I1 
33330P1 
34441A11
34441B1 
34441C1 
34441I1 
34441P1 
35552A11
35552B1 
35552C1 
35552I1 
35552P1 
36663J2 
36663K1 
36663M1 
37774A11
37774B1 
37774C1 
37774I1 
37774P1 
38885A11
38885B1 
38885C1 
38885I1 
38885P1 
39996A11
39996B1 
39996C1 
39996I1 
39996P1 
41107A11
41107B1 
41107C1 
41107I1 
41107P1 
42218A11
42218B1 
42218C1 
42218I1 
42218P1 
43329L10
44440B10
44440M1 
45551Q00
46662Q00
47773Q00
48884Q00
49995A10
5555555A-2-2
5555555B-2 
5555555C-2 
Grand Total 24834

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

 

InvoiceCOMPONENTUnits
1111A1
2222A1
3333A1
4444A1
5555A1
6666A1
7777A1
8888A1
9999X1
9999X1
11110A1
12221A1
9999A1
9999A1
13332A1
14443A1
15554A1
16665A1
17776A1
11110X1
11110X1
12221X1
12221X1
7777X1
7777X1
17776X1
17776X1
11110Y1
7777Y1
12221Y1
12221Y1
9999Y1
16665X1
16665X1
11110Y1
9999X1
12221X1
11110X1
12221X1
9999X1
18887Z1
12221D1
9999D1
16665D1
7777D1
11110D1
17776D1
12221B1
11110X1
2222Y1
19998Y1
21109Y1
5555Y1
1111Y1
22220Y1
23331A1
24442A1
25553A1
25553A1
26664F1
26664A1
27775B1
26664C1
11110X1
12221X1
28886A1
12221X1
11110X1
19998B1
25553B1
23331B1
24442B1
29997B1
28886B1
23331B1
25553B1
21109B1
31108B1
22220B1
32219B1
33330B1
8888B1
34441A1
35552A1
26664H1
2222B1
22220I1
25553I1
26664I1
25553I1
11110I1
28886I1
9999I1
14443I1
29997I1
36663J1
33330I1
12221I1
31108I1
37774I1
17776I1
26664F1
36663K1
16665I1
8888I1
33330A1
31108A1
22220A1
38885I1
39996I1
32219I1
6666I1
1111I1
5555I1
4444B1
3333B1
19998I1
2222I1
37774A1
21109A1
19998A1
23331A1
41107A1
29997A1
27775A1
42218A1
28886C1
14443C1
37774C1
39996C1
16665X1
16665X1
43329L1
26664M1
26664F1
26664M1
16665B1
14443B1
41107B1
37774B1
39996B1
36663J1
26664N1
26664N1
26664B1
5555B1
42218B1
35552B1
34441B1
6666B1
17776B1
1111B1
38885B1
22220O1
5555O1
19998O1
21109O1
2222O1
22220O1
1111O1
21109O1
2222O1
17776Y1
7777Y1
7777X1
7777X1
16665Y1
16665Y1
17776Y1
9999Y1
9999X1
9999X1
11110B1
9999B1
7777B1
16665C1
8888C1
17776C1
32219C1
31108C1
22220C1
33330C1
38885C1
42218C1
27775C1
38885P1
41107P1
29997P1
32219P1
34441P1
14443P1
6666P1
25553P1
37774P1
24442P1
27775P1
42218P1
28886P1
39996P1
31108P1
25553P1
35552P1
23331P1
23331P1
8888P1
33330P1
17776X1
16665X1
17776X1
16665X1
17776X1
17776X1
7777X1
7777X1
44440M1
36663M1
4444C1
3333C1
4444I1
3333I1
45551Q1
46662Q1
46662Q-1
47773Q1
47773Q-1
45551Q-1
48884Q-1
48884Q1
44440B1
34441C1
7777C1
35552C1
6666C1
5555C1
39996A1
49995A1
23331I1
21109I1
7777I1
24442I1
35552I1
42218I1
41107I1
23331I1
34441I1
27775I1
21109C1
23331C1
25553C1
25553C1
29997C1
11110C1
2222C1
24442C1
12221C1
41107C1
23331C1
9999C1
1111C1
19998C1
32219A1
38885A1
5555555A-1
5555555B-1
5555555C-1
5555555A-1
5555555B-1
5555555C-1

 

 

Correct Output

InvoiceBundle Count
11111
22221
33331
44441
55551
66661
77771
88881
99991
111101
122211
133320
144431
155540
166651
177761
188870
199981
211091
222201
233312
244421
255532
266641
277751
288861
299971
311081
322191
333301
344411
355521
366630
377741
388851
399961
411071
422181
433290
444400
455510
466620
477730
488840
499950
5555555-2
Grand Total34
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

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 =
    	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?

Greg_Deckler
Super User
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))

 

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

InvoiceBundles (Measure)
1111
2222
3330
4440
555-1
Total2
 
 
 
Anonymous
Not applicable

Hello

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

scr.png

 

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

scr.pngscr1.png

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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