Show Delta between a selected item from a Column and the other rows items

[Anonymous]

Hi,

I need to provide a visualization and a table of the delta between one selected item's value and all the others. (p.s.: the table is used in other views and I cannot directly pivot or change it at source).

Source

item	scenario	value
A	WWW	3
A	EEE	5
A	TTT	1
B	WWW	3
B	EEE	6
B	TTT	1
C	WWW	7
C	EEE	5
C	TTT	1
D	WWW	3
D	EEE	5
D	TTT	1

Selection : A

Expected outcome:

Outcome
item	scenario	Delta
B	EEE	1
C	WWW	4

(and the same outcome to be shown in a column chart)

Any ideas?

Thanks,

Pietro

[mahoneypat · Accepted solution]

To do this, you'll need to make a disconnected table of your Items to be used in the slicer.  You can then use this measure to get your desired result.

Slicer = DISTINCT(Delta[item])

Show Delta =
VAR selitem =
    SELECTEDVALUE ( Slicer[item] )
VAR thisvalue =
    SUM ( Delta[value] )
VAR thisscenario =
    MIN ( Delta[scenario] )
VAR selvalue =
    CALCULATE ( SUM ( Delta[value] ), Delta[item] = selitem )
VAR result = thisvalue - selvalue
RETURN
    result

Pat