Amount for all possible Customer using P()

[Ipsita20191]

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]

Hi @Ipsita20191 ,

Maybe this:
sum( {< [Customer] = P( {1< [Shop] = {'Shop A'} ) * P( 1< [Shop] = {'Shop B'} >} ) >} [Sales Amount] )

to get the amount for customers shopped in both A and B

I hope it can helps.

Best Regards

[Ipsita20191]

No It is still giving the only for Shop B Amount

[Ipsita20191]

As I have been using Shop as dimension as left side in the table, Value always giving for that Shop B.

I want to get the value for Shop A + Shop B for Dimension row Shop B

[agigliotti]

That expression should works in a text box.

Let's give a try and let me know.

Best Regards

[agigliotti]

could you post a sample app with mock data?

[Ipsita20191]

I have attached QVF file here with sample data. Data from Test Data file has been used.

1st you have to select drop down shop.

For Eg If you select Shop A , Then we have to see howmany customers have shopped in Both Shop A and Shop B then Shop A and Shop C and so on.

If you see in App I have found out how many possible customers have shopped in both Shop.

But If you see the Amount they spent , It is not coming correct.

If you see Shop A from variable Input and Shop B from Table , 123 and 456 has shopped in both Shop A and Shop B thats y count is showing 2 in "Shopped in Both" Column.

But If you see the Amount 2 customers  spent is 1000 in both shops . That I am not able to find out.

[Ipsita20191]

Can anyone please help me here. I have attached data set and App

[agigliotti]

Hi @Ipsita20191 ,

You are getting the correct result because users 123 and 456 have purchased 1000 in shops A and B.
You actually get the sum of 1000 as 300 (Shop A) and 700 (Shop B).

I hope it can helps.

Best Regards

[Ipsita20191]

I did not get your point. I can only see 700 in 2nd row for B. Where I want 1000.