cancel
Showing results for
Did you mean:
Helper I

## Select "Groups" of Measures in a Slicer

Hi,

I have data in a format similar to below:

 Unique ID Current Status Status 1 Start Status 2 Start Status 3 Start Status 3 Finish Status 1 Time Status 2 Time Status 3 Time AA 1 3-Mar-20 AB 2 11-Mar-20 16-Mar-20 5.00 AC 2 11-Mar-20 18-Mar-20 7.00 AD 2 4-Mar-20 10-Mar-20 6.00 AE 2 4-Mar-20 11-Mar-20 7.00 AF 3 2-Mar-20 9-Mar-20 16-Mar-20 7.00 7.00 AG 3 1-Mar-20 14-Mar-20 20-Mar-20 13.00 6.00 AH 4 20-Feb-20 1-Mar-20 9-Mar-20 20-Mar-20 10.00 8.00 11.00

The relevant info from this is the average amount of time spent per unique ID per status over a time period, and also the max time spent per unique ID per status, and I am trying to display the metric for each status at the same time, but also have the ability to toggle between the two measures.

Is there a way I can create a measure for the average of each status (so a "Avg_1, Avg_2, Avg_3, Max_1, Max_2, Max_3") and group them together in a way that I can create a slicer where I can select "Average" and it would update all of my charts to show the average measures, and if I select "Max", it would do the same for the max measures.

For example, if I had a card for status 1, status 2 and status 3:

If I selected "Average":

Status 1: 7.85

Status 2: 7.00

Status 3: 11.00

If I selected "Max":

Status 1: 13.00

Status 2: 8.00

Status 3: 11.00

And then in the end, I would display these numbers as a product of time where I have 3 charts showing the trend of each status, and I can toggle between the average and max for each of the 3 statuses over time on 3 different charts.

Please let me know if this is possible, and if I explained well enough.

1 ACCEPTED SOLUTION
Community Support

Hi @Andrew17 ,

You could create a slicer table at first. Then create three similar measures to show the result.

``````Measure 1 =
VAR a =
SELECTEDVALUE ( Slicer[Column1] )
VAR b =
MAX ( 'Table'[Status 1 Time] )
VAR c =
AVERAGE ( 'Table'[Status 1 Time] )
RETURN
IF ( a = "MAX", b, c )``````

Then use card visual. Here is the result. You could also use bookmark to switch your results.

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.
3 REPLIES 3
Community Support

Hi @Andrew17 ,

You could create a slicer table at first. Then create three similar measures to show the result.

``````Measure 1 =
VAR a =
SELECTEDVALUE ( Slicer[Column1] )
VAR b =
MAX ( 'Table'[Status 1 Time] )
VAR c =
AVERAGE ( 'Table'[Status 1 Time] )
RETURN
IF ( a = "MAX", b, c )``````

Then use card visual. Here is the result. You could also use bookmark to switch your results.

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Frequent Visitor

Hello Andrew,

To achieve it, I created a new table as below.

Then Created Measures for each status and assigned those measure values to the above table as a new calculate column.

Then, used this table in the chart with Type as Slicer.

Attached the link for solution below.

Thanks,

James

Helper I

Hi @Jamesmathew ,

Thanks for your response, this is definitely pushing me in to the right direction!

I'm trying to get this solution to show the trend of the data (based on the date fields) Is there a way to manipulate the data to show a line chart with the date in the x axis?

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!