Need help on how to calculate sales based on moving values from one slicer to other.

[Mahi1827]

Need help on below scenario, I have table  and slicers like below.

	Table 1
Owner	Product	Sales
A	p1	4000
A	p2	6000
A	p3	10000
A	p4	2000
B	p5	8000
B	p6	3000
B	P7	9000

Slicer 1(created table with distinct values of Owner from table 1)

Old Owner

A

B

Slicer 2 (created table with distinct values of product from table 1)

Product

P1

P2

P3

P4

Slicer 3 (duplicate copy of Slicer 1 for user display purpose)

New Owner

A

B

Requirement is to show updated sales with respect to B when particular prodcuts changed from A to B in case 1 & case 2 scenarios as below.

For example when i select Owner A from Slicer 1 and select p1 & p2 prodcuts from Slicer 2
and if i assign p1 & p2 to B by selecting in slicer 3 need to analyze the sales with respect to owner A and owner B

case 1: expected output table or chart: Analyzing sales prodcuts changes from Owner A to B, along with existing prodcuts of B

Newowner  old&newproduct    updatedsales1
B                 p5                             8000 (exsiting prodcut sales of B)
B                p6                              3000 (exsiting prodcut sales of B)
B                p7                              9000 (exsiting prodcut sales of B)
B                p1                              4000 ( p1 sales(4000) newly assigned from A to B)
B                p2                              6000 ( p2 sales(6000) newly assigned from A to B)

case 2: expected output table or chart : Need to show what are remaining products from Owner A list,in slicer 2 products p1 & p2
are moved to Owner B then remain prodcuts P3,p4, here analysing existing prodcuts how much sales it is.

Oldowner   product updatedsales2
A                p3           10000 ( as per table 1 data)
A                p4            2000  ( as per table 1 data)

created measure for case 1 like below but didnt get expected output 

updatedsales1= var previousowner=selectedvalue(products1[owner]) ----slicer 1 selection
                          var product=Allselected(products[product]) --slicer 2 selection (single or multiple selection)
                           var newowner=selectedvalue(products2[owner]) ----slicer 3 selection
return calculate(sum(Table1[sales]),FILTER(Table1[Owner]=previousowner))
+calculate(sumX(Table1[sales]),FILTER(Table1[product]=product and FILTER(Table1[Owner]=newowner)))

created  measure for case 2 like below but didnt get expected output.

updatedsales2=calculate(sum(table1(sales),except(all(table1([product]),allselected(table1([product])))

by using above measure i am getting overall sum 12000 but i need to split products p3 p4 ( p3 10000,p4 2000)  like above case 2

Please go through the deatils, help me and suggest with updated DAX measures in above 2 cases with proposed solutions.

Thank you for your time and help in advance.

Thank you,

Mahi18

[v-diye-msft]

Hi @Mahi1827 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[mahoneypat]

One way to do this is to make a second table of your Owner table and use the Basket Analysis pattern described by SQLBI here.

https://www.daxpatterns.com/basket-analysis/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat