Adjust values based on slicer state

[Anonymous]

I have a dataset with time series, a value, and a location.

datetime	value	location
26-2-2019 12:15	4	A
26-2-2019 12:30	23	A
26-2-2019 12:45	2	A
26-2-2019 13:00	62	A
26-2-2019 13:15	3	A
26-2-2019 12:15	6	B
26-2-2019 12:30	45	B
26-2-2019 12:45	8	B
26-2-2019 13:00	8	B
26-2-2019 13:15	2	B
26-2-2019 12:15	3	C
26-2-2019 12:30	4	C
26-2-2019 12:45	4	C
26-2-2019 13:00	6	C
26-2-2019 13:15	4	C

This data is about electricity meters.

For these time series a have a bar/line chart for the sum(value) of all locations per time interval.

And a bar chart with sum(value) per location.

For analysis purposes, I want to be able to invert the value (value * -1) per location.

Ideally, I can use a standard slicer visual, with a list of all the locations, if a location is unselected the data shown is normal, if a location is selected the data for only the selected locations are inverted (value * -1) and the data is not filtered like the default behaviour of a slicer.

Any ideas?

[v-cherch-msft]

Hi @Anonymous 

You may create a slicer table: Slicer = DISTINCT(Table2[location]).Then use it as slicer.Then you may get the measure like below:

invert = 
IF (
    MAX ( Table2[location] ) in VALUES(  Slicer[location] ),
    SUM ( Table2[value] ) * ( -1 ),
    SUM ( Table2[value] )
)

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft 

Thank you. But it doesnt seem to work like i want yet.

In the example pbix you attached;

* if i select no location (ie all values should be original) then all values are * -1. it does work correctly if one or more locations are selected. i managed to fix this myself by putting your code into an IF ISFILTERED

* if i make a column chart with "datetime" on the axis, and "invert" on the values, then it does not behave like i expected. this chart is the ultimate goal of my report. i guess that is because location is not in the chart? is there a way around this?

Bonus question:

The slicer table does not seem to respond to filters. Is that possible? What if there was more data and because of an (external) filter there were less locations?

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a measure like below and use it for the datetime visual.

Measure 2 = 
SUMX(FILTER(Table2,Table2[datetime]=MAX(Table2[datetime])),[invert])

Regards,

Cherie

[Anonymous]

Fantastic it works thank you. Can you please explain the last measure?

SUMX(FILTER(Table2,Table2[datetime]=MAX(Table2[datetime])),[invert])