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?
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]
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.