SUM SALES WITH PROMO

[Marcox28]

I have a problem in calculating sales made with receipts with at least one product being promoted.

my sales table is

TVE_ID	TVE_IdArticolo	TVE_Price	TVE_CodicePromozione	TVE_IDScontrino
1	838968	3,95	444	1
2	854978	2,03		1
3	850875	10		1
4	853576	27,99		2
5	853576	27,99		2
6	810203	27,99		2
7	853576	27,99		3
8	810203	27,99	444	4
9	849757	10		4
10	838366	2,99		4
11	838366	2,99		4
12	838366	2,99		5
13	855144	2,49		5
14	822673	3,59		5
15	835175	19		5
16	836634	23,92		5
17	834261	38,5		6
18	834308	6,47	444	7
19	854963	2,03		8
20	854966	2,03		8
21	854967	2,03		8
22	854960	2,73		8
23	847837	27,9		9
24	834330	14,97	444	9
25	815823	19,95		9

where is it

TVE_ID indicates sales line ID

TVE_price indicates the selling price

TVE_codicepromo indicates the code of the promotion

TVE_IDScontrino indicates the receipt number.

How can I calculate the sum of all receipts that contain at least one product in promotion 444?

I expect this result to come: promotional sales = 129.24

because it sum the following sales line

TVE_ID	TVE_IdArticolo	TVE_Price	TVE_CodicePromozione	TVE_IDScontrino
1	838968	3,95	444	1
2	854978	2,03		1
3	850875	10		1
8	810203	27,99	444	4
9	849757	10		4
10	838366	2,99		4
11	838366	2,99		4
18	834308	6,47	444	7
23	847837	27,9		9
24	834330	14,97	444	9
25	815823	19,95		9

[Anonymous]

I'm confused. I see only four lines coded to the promo and they do not add over $100. If there is no code in the lines I do not know how to pull those out.

[Marcox28]

yes, four lines that are part of 4 receipts containing, in some cases also products not sold in promotion. I would also like to add those lines as you can see from the second table.

the sales lines are part of the same receipt if they have the same TVE_IDScontrino

[popov]

Hello, @Anonymous
If i correct understud your case, i hope formula below help you.

Promotional sales =
CALCULATE (
    SUM ( SalesTable[TVE_price] ),
    SUMMARIZE (
        FILTER ( SalesTable, NOT ISBLANK ( SalesTable[TVE_codicepromo] ) ),
        SalesTable[TVE_IDScontrino]
    )
)

[Anonymous]

That only adds up where there is a promo code. The Measure needs to select TVE_IDScontrino where Promo is not null and add the Sales for the rows with those TVE_IDScontrino.

I'm thinking it's a column that brings in the TVE_IDScontrino where TVE_CodicePromozione NOT ISBLANK. Then use that column for a Measure to Sum.

TVE_IDScontrino	TVE_Price
1	15.98
4	43.97
7	6.47
9	62.82
	129.24

[Marcox28]

this formula is not correct for me because it only returns the sum of the rows that have TVE_codicepromo 444.

so with result 53.38.

while I need to calculate the sum of all receipts that contain at least one product in promotion then with a total of 129.24

[Marcox28]

TVE_ID	TVE_IdArticolo	TVE_Price	TVE_CodicePromozione	TVE_IDScontrino
1	838968	3,95	444	1
2	854978	2,03		1
3	850875	10		1
8	810203	27,99	444	4
9	849757	10		4
10	838366	2,99		4
11	838366	2,99		4
18	834308	6,47	444	7
23	847837	27,9		9
24	834330	14,97	444	9
25	815823	19,95		9

the color of the characters helps you to understand that the lines of the same character are part of the same receipt. I can distinguish them through the TVE_IDScontrino

[parry2k]

@Anonymousin my opinion @popov measure should work. I guess you tried and not worked, can you share how you did it?

[Anonymous]

I simply copied the example table in the original post into BI. I then created a Measure using the formula given. It only brings in the Sum of the four rows with the Promo Code. It ignores the receipts for the Promo where the promo is Null. 

[parry2k]

Seeme like somethign else is going on, I tested it on my end with sample data and it worked fine.

[Anonymous]

Did you get 129.24 ?

Here's the code:

Promotional sales =
CALCULATE (
SUM ( SalesTable[PRICE] ),
SUMMARIZE (
FILTER ( SalesTable, NOT ISBLANK ( SalesTable[TVE_CodicePromozione] ) ),
SalesTable[TVE_IDScontrino]
)
)

Here is the result: It only pulls in the four rows with the PromoID

TVE_ID	Promotional sales	PRICE
1	3.95	3.95
2		2.03
3		0.1
4		27.99
5		27.99
6		27.99
7		27.99
8	27.99	27.99
9		0.1
10		2.99
11		2.99
12		2.99
13		2.49
14		3.59
15		0.19
16		23.92
17		3.85
18	6.47	6.47
19		2.03
20		2.03
21		2.03
22		2.73
23		2.79
24	14.97	14.97
25		19.95

[parry2k]

Sorry when I imported the data, you have comma which turn out to be number for not decimal but anyhow you have the idea that it works, not sure why you have price and id in the table. 

[Anonymous]

Price is a Measure dividing the Imported price with a Comma by 100 to get the values in the example.

You are not getting the right value. The value in your example should be 12,924. You are not picking up all the sales for the receipt IDs.

TVE_ID	TVE_IdArticolo	TVE_Price	TVE_CodicePromozione	TVE_IDScontrino
1	838968	3.95	444	1
8	810203	27.99	444	4
18	834308	6.47	444	7
24	834330	14.97	444	9
2	854978	2.03	These are NULL but still need to be added in	1
3	850875	10	1
9	849757	10	4
10	838366	2.99	4
11	838366	2.99	4
23	847837	27.9	9
25	815823	19.95	9
		129.24

[parry2k]

attached, check and let me know what we are missing.

[Anonymous]

Sorry parry. You are not listening. My latest example was quite explicite. This shouldn't be that hard but it is. Rather fun. 

[parry2k]

although I found your reply bit rude "you are not listening", dude we all are helping each other here and I'm doing my best to get what you are lookig for.

[Anonymous]

Sorry, you are right. 

You insist that the 84 is the right number. It is not.  All Receipt IDs of 1,4,7,9 need to be added in despite that only one row for each ID has a promo code in it. 

That requires a nested "query" to pull out the Receipt ID for promos and then sum all rows with those receipt IDs. 

[parry2k]

Anyhow, I guess you have the solution, you have to look data in my pbix file and then just use and apply on your data. Good luck. 

[popov]

I think, I know what the problem. You are filtering data by column TVE_codicepromo. This formula is correct:

Promotional sales =
CALCULATE (
    SUM ( SalesTable[TVE_price] ),
    SUMMARIZE (
        FILTER ( SalesTable, NOT ISBLANK ( SalesTable[TVE_codicepromo] ) ),
        SalesTable[TVE_IDScontrino]
    ),
    ALL ( SalesTable[TVE_codicepromo] )
)

[Greg_Deckler]

@Marcox28, @Anonymous, @parry2k, @popov - In response to a private message, I believe the measure that you are looking for is this:

Sum in Promo = 
VAR __values = SELECTCOLUMNS(FILTER(table9,[TVE_CodicePromozione]=444),"__SearchValues",[TVE_IDScontrino])
VAR __tmpTable = ADDCOLUMNS('Table9',"__In",IF([TVE_IDScontrino] IN __values,TRUE,FALSE))
RETURN SUMX(FILTER(__tmpTable,[__In]),[TVE_Price])

Returns $129.24 with the data provided in the original post. 

Walking through the code:

• First line gets the values of IDScontrino that have at least one item matching 444 as the promo code.
• Creates a temporary table that returns the original table with an added column of either TRUE or FALSE. This additional column is calculated such that it checks if the IDScontrino in each row is "IN" the previously computed values table returned in the first line. If so, then TRUE, if not then FALSE
• Does a simple SUMX on the table filtered such that "__In" is TRUE.

[Anonymous]

You are the best Greg. I knew it most likely relyed on a column or a view (temp table). 

Question: How do we pull in the TVE_IDScontrino for any Promo (NOT ISBLANK) so the promo number doesn't have to be manually in the script? I failed.

Thank you. This is much needed to this old SQL guy who lived by nested queries to solve such things.