Comparing two states

[Fali324]

Hi

My data is structured in the following way:

Project	Stage	Cost
A	1	10
A	2	20
B	1	10
B	2	40
B	3	30
C	1	10

For each project I want to compare the stages however I want to select the two stages I want to compare, so I would like slicer 1 to select a stage and slicer 2 to select a stage so I can do comparative measures. 

E.g. for Project B I may compare stage 1 to stage 2 or stage 2 to stage 3 or stage 1 to stage 3

How can I achive this?

Any help will be much apricated

Thanks,

[Bibiano_Geraldo · Accepted solution]

Hi@Fali324 ,

You can achieve your goal bt these steps:

1 - Create two tables for slicer 1 and slicer 2 by this DAX:

Slicer 1 = VALUES(YourTable[Stage])

Slicer 2 = VALUES(YourTable[Stage])

2- Make sure tha they are NOT related:

3 - Add a new slicer and add a project column from YourTable 

4- Now add two slicer, in the first one, add the stages from the Slicer 1 Table and in the second Slicer add the Stages from the Slicer 2 Table

Now add a measure to compare the cost value, in my case i'm just subtracting Slicer 1 - Slicer 2, feel free to adapt to attend your needs:

Diference = 
VAR totalSlicer1 = 
CALCULATE(
    SUM(YourTable[Cost]),
    FILTER(
        YourTable,
        YourTable[Stage] = SELECTEDVALUE('Slicer 1'[Stage])
    )
)

VAR totalSlicer2 = 
CALCULATE(
    SUM(YourTable[Cost]),
    FILTER(
        YourTable,
        YourTable[Stage] = SELECTEDVALUE('Slicer 2'[Stage])
    )
)

RETURN
totalSlicer1 - totalSlicer2

Add this Matrix for card or table, and your result will look like this:

[Fowmy]

@Fali324 

Create additional dimension tables with no relationship for stage and use in a separate slicer, use a measure to capture the value selected from each visual and compare as you need.