I have a simple single table dataset which has columns for Customer, Product, and Sales. I want to compare customers head to head based on sales for different products.
What I've tried so far: I duplicated the Name column twice (to form Name1 and Name2), and have a slicer based on each that are set to ignore each other.
I'm struggling to create measures to sum sales based on those slicers. This is what I tried:
SalesPick1 = if(HASONEVALUE('Table'[Name1]), CALCULATE(sum('Table'[Sales]), REMOVEFILTERS('Table'[Name2])))
SalesPick2 = if(HASONEVALUE('Table'[Name2]), CALCULATE(sum('Table'[Sales]), REMOVEFILTERS('Table'[Name1])))
Then I have a matrix with rows based on Product, and two value columns, Salespick1 and Salespick2. If I select a name from one of the slicers and leave the other blank, the columns work. If I select different values for the two slicers, I get no data in my matrix.
I want this:
Product Sales for Pick1 Sales for Pick2
A x1 y1
B x2 y2
C x3 y3