Need help on show cumulative sales in chart based on multiple values from slicer data

[Mahi1827]

I have 3 slicers and selected data as below

Previous Owner slicer

A

Product slicer
p1
p2
p3
p4

New Owner Slicer

B

----------Table1

product  Owner  total sales
p1          A          4000
p2          A          6000

overall total sales from for prodcuts p1(4000),p2(6000),p3(10000),p4(5000) as below

----------table 2
Owner, total sales
A           25000

-----------------------------------------------------------------------

here need to visualize sales when owner chnaged from A to B,need to show total sales in chart with respect to new owner B. (adding cumulative sales)

I have created sales measure like below (prodocts,product1,product2 all 3 tables are distinct value tables)

-----------------------------------------------------------------------------------------------

sales for new owner= var product=selectedvalue(products[product] --exmple p1
                                  var previousowner=selectedvalue(products1[owner]) ----exmple A
                                  var newowner=selectedvalue(products2[owner]) ----exmple B

return calculate(sum(Table1[Totalsales]),FILTER(Table1[Owner]=previousowner)) +calculate(sum(Table2[Totalsales]),FILTER(Table2[Owner]=newowner)) --example(4000+25000)

above measure not working when i select multiple products for cumulative sales--example (6000+(29000(4000+25000)=35000)
--here i need to show cumulative sales for new owner B like below and p1,p2,p3,p4 all are assigned to A owner and overall sales for A is 25000 like table 2

expectd output is

product defaultoverallsales newsales   salesfornew owner
p1         25000                     4000          29000 (25000+4000)
p2         25000                     6000        35000 (6000+29000)

when i am selecting only one product from product slicer p1, i am getting only 1 row like below 

product default sales new sales    sales for new Owner
p1         25000            4000           2900000

this is fine but above measure (sales for new owner) is not working when i select multiple products like p1,p2 to get cumulative totals.

finally i need to showcase owner B sales data in chart as below based on the Product slicer selection (example if i select p1 and p2 then my chart will be)

p1  29000
p2  35000

(x axis as sales,y axis as product,legend as owner, vlaues p1 value 4000 will add to defalut sales (25000) chart result for p1 is 29000,and again p2 sales 6000 now it going to add new existing sales of p1 that is )

6000+290000  i.e 35000.

so can you pleas help me how to modify sales for new owner measure for select multiple products like p1 and p2 to show cumulative sales for Owner B.

i have tried with by changing selectedvalue to "all selected" and "all" but not getting expected output. please help me in this scenario.

Thanks
Mahi18

[v-xicai]

Hi @Mahi1827   ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Greg_Deckler]

@Mahi1827 Sorry, I am not following this, can you share the PBIX? 

[Mahi1827]

Hi Greg_Deckler,

Thank you for your time and reply. Please find below notes on my requiremnt 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.

Thanks,
mahi18

[v-xicai]

Hi @Mahi1827 ,

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

New Owner

A

B

Could you please clarify more why you create the duplicated slicer1? So far, confused to get the logic to gain your desired output.  Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

If you need to get cumulative total sales for per Owner, you may enter into Query Editor to add an index column [Index] firstly, then create measure like DAX below.

Cumulative total =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[Owner] = MAX ( Table1[Owner] )
            && Table1[Index] <= MAX ( Table1[Index] )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.