cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Select "Groups" of Measures in a Slicer

Hi,

 

I have data in a format similar to below: 

 

Unique IDCurrent StatusStatus 1 StartStatus 2 StartStatus 3 StartStatus 3 FinishStatus 1 TimeStatus 2 TimeStatus 3 Time
AA13-Mar-20      
AB211-Mar-2016-Mar-20  5.00  
AC211-Mar-2018-Mar-20  7.00  
AD24-Mar-2010-Mar-20  6.00  
AE24-Mar-2011-Mar-20  7.00  
AF32-Mar-209-Mar-2016-Mar-20 7.007.00 
AG31-Mar-2014-Mar-2020-Mar-20 13.006.00 
AH420-Feb-201-Mar-209-Mar-2020-Mar-2010.008.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.

 

I appreciate your help 🙂

1 ACCEPTED SOLUTION
Community Support
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. 

1-1.PNG

Here is my test file for your reference.

 

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

View solution in original post

3 REPLIES 3
Community Support
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. 

1-1.PNG

Here is my test file for your reference.

 

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

View solution in original post

Frequent Visitor

Hello Andrew,

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

 

Capture1.PNG

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

 

Capture2.PNG

 

Capture3.PNG

 

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

 

 

Attached the link for solution below.

https://1drv.ms/u/s!AlTyyZ_9D_WjimMgrhfU3pGS-hbq 

 

Thanks,

James

 

 

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? 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors