Amount for all possible Customer using P()

Ipsita20191 · ‎2022-10-05

Shop Selection	A
	Customer Count	Sales Amount	Current Result	Expected Result
Shop A	100(Only 100 customer shopped in A)	$5000 (100 Customer Spend in Shop A)	5000	5000
Shop B	50(50 customers shopped in both A and B)	1000(This Amount 50 Customer Spent for Shop B ) + $1200 Amount spent for Shop A	1000	2200
Shop C	40(40 customers shopped in both A and C)	1001(This Amount 40 Customer Spent for Shop C ) + $1220 Amount spent for Shop A	1001	2221
Shop D	30(30 customers shopped in both A and B)	1002(This Amount 30 Customer Spent for Shop D) + $1230 Amount spent for Shop A	1002	2232

	Function Used	sum({$<[Customer] = P({1<[Shop]={'Shop A'}>} )>} [Sales Amount] )

	I want total sales Amount those common Customer spent in Both Shops

agigliotti · ‎2022-10-11

I think the correct amount expression for your scenario is:

sum( {< CustomerID = P( {< Shop = {'$(V1)'} * P(Shop) >} CustomerID ) >} [Sales Amount] )

It returns the amount spent by customers in the shop selected in the variable and the current shop.

I guess you can't get the value 1000 in row B with that data model.

Best Regards