powerbidaxpowerbi-desktop

2 slicers, same column of same data. Find difference between sliced results


Very new to PBI, so appreciate the help.

DESCRIPTION
Given a single dataset of two columns, TABLE_1:

SOURCE VALUE
A 10
A 20
A 30
B 5
B 10
B 15
C 1
C 2
C 3

I have 2 single select slicers that operate on the SOURCE column to filter side by side charts in my report.

PROBLEM
I want to create a card which displays the difference between the sum of VALUE for SOURCE selected by the 2 slicers.

However, because both slicers operate on the same column from the same set, I can't seem to create a measure or card that functions as desired.

POTENTIAL SOLUTIONS
I have attempted to create a measure using SELECTEDVALUE:

Difference = 
VAR SelectedOption1 = SELECTEDVALUE('TABLE_1'[SOURCE])
VAR SelectedOption2 = SELECTEDVALUE('TABLE_1'[SOURCE])
RETURN
CALCULATE(
    SUM('TABLE_1'[VALUE]),
    'TABLE_1'[SOURCE] = SelectedOption1
) - 
CALCULATE(
    SUM('TABLE_1'[VALUE]),
    'TABLE_1'[SOURCE] = SelectedOption2
)

But this produces 'Blank' when the 2 slicers select different options for SOURCE. Or 0 when the same is selected (obviously)

My only working solution so far involves creating a copy of TABLE_1 and associating a single slicer with a single table. Is there a solution that can get around this need for a duplicate table?


Solution

  • DAX may be tricky in your case as you can't rely on one slicer from the same table.

    Create 2 calculated tables based on your original one to be used in seperate slicers:

    SlicerOptions1 = DISTINCT(SELECTCOLUMNS(TABLE_1, "SOURCE", TABLE_1[SOURCE]))
    
    SlicerOptions2 = DISTINCT(SELECTCOLUMNS(TABLE_1, "SOURCE", TABLE_1[SOURCE]))
    

    Then create the following measures :

    SelectedSum1 = 
    VAR SelectedSource1 = SELECTEDVALUE(SlicerOptions1[SOURCE])
    RETURN
        CALCULATE(
            SUM(TABLE_1[VALUE]),
            TABLE_1[SOURCE] = SelectedSource1
        )
    
    SelectedSum2 = 
    VAR SelectedSource2 = SELECTEDVALUE(SlicerOptions2[SOURCE])
    RETURN
        CALCULATE(
            SUM(TABLE_1[VALUE]),
            TABLE_1[SOURCE] = SelectedSource2
        )
    
    Difference = [SelectedSum1] - [SelectedSum2]
    

    enter image description here

    Don't forget to edit interactions between the 1st slicer and make it to not impact the 2nd measure and the same for the 2nd slicer and make it to not impact the 2nd measure.

    enter image description here

    enter image description here