KPI Visual - How to compare one point to rest of filtered data using measures

[brspencer90]

Hi all - 

Looking to get some advice on a problem I'm running into currently. I have an large table that I'm visualising a subset of that data on a line chart. Slicers are below using enumerated data instead of label names for privacy purposes.

The goal of this page is to be able to select a single point of the subset of data, and be able to compare that single point to the rest of the points (inclusive of single point). I've created an independent but duplicate copy of the original table (named 'test') and have creatively named it 'Table' while I'm working this out.

I currently have the example measure in place for all three of the slicer variables (component, pump, and status) : 

The measure will get the distinct values of the slicers from the original and store that in comp_list. Then filter the table if the component id is in the selected slicer list, assigning it value. I then can filter out the values that aren't in the slicer by using a visual filter as below : 

When I plug the datapoints into a table visualisation I'm geting the appropriate value for the median duration. However, when I do the exact same thing for the KPI visualisation, I'm not getting the correct data. The Overall Median is for ALL tests - including other values that are outside of the slicer selections.

Does anyone have any advice on how to solve this problem?

[brspencer90 · Accepted solution]

Here's a good one, I ended up using timestamp_start on the chart on the left, and timestamp_end for the KPI gauges.

Swapped KPI gauges to timestamp_start, and the values are changing with the filters as expected.

There are some data inconsistencies which I'm trying to solve right now, but this is the major issue that has been solved.

[v-eqin-msft]

Hi @brspencer90 ,

Not very clear. 

Can you please share more detail information(data sample , expected output...) to help us clarify your scenario?

Best Regards,
Eyelyn Qin

[brspencer90]

Hi Evelyn - 

Example (filtered by slicer) data : 

Datetime	Duration	Volume	Component	Pump	Status
22-Aug 12:00 PM	30	74	3	1	1
23-Aug 12:00 PM	29	73	3	1	1
24-Aug 12:00 PM	30	76	3	1	1
25-Aug 12:00 PM	31	77	3	1	1
26-Aug 12:00 PM	40	72	3	1	1
27-Aug 12:00 PM	120	74	3	1	1
28-Aug 12:00 PM	38	73	3	1	1

When I plot it in the chart (without selecting a single data point), I get the following medians for duration and volume.

duration : 30

volume : 74

The function I have made is supposed to use the same filters as the subset (plotted) data and return these values when a single point is selected on the line chart visual. I am able to get the correct value to show up on a data table visual, but not in the actual KPI Gauge that I need it to show up in (as shown above).

The KPI Gauge Data Fields are as follows : 

Where :

duration = original table - single point selection

duration_median = measure to take the median of the secondary independent table

duration_median (current measure code) : 

On the KPI Gauge, I get overall median for ALL of the data in the datasets regardless of the measure filters that I have in place.