Filter and Average based on category

[Anonymous]

Hi all,

I'm trying (and failing!) to pull and display the average of one column, based on the identified category from another - reactive to a Filter or Slicer at the page level. 

Data structure looks like;

Account ID	Account Name	Grand Total	ACV	ACV Rank	Employee Bucket
0014000000aWncBAAS	Bob	23000	15000	885	a) 1-2
0014000001llJaVAAU	Jeff	4457865	0	1142	b) 3-4
0014000001llJb4AAE	Randy	4468477	941389	29	a) 1-2
001300000089l9FAAQ	Michael	280492	0	1143	c) 5-6
0014000000diMnNAAU	Wendy	22336	19336	731	a) 1-2
0014000001llGWFAA2	Neha	746736	0	1144	b) 3-4

And what I'm trying to do is give the user an option to Filter or Slice by name, and then have a Card (or Multi-Row Card) display the average of the 'Grand Total' Column for the accordant category that the name is in in the 'Employee Bucket' category.

ie I'd like a user to filter/slice for 'Wendy', and then have a card display the average (ideally median) of all 'Grand Total's in the 'Employee Bucket' category 'a 1-2) - taking the average of Bob + Randy + Wendy, all of whom are in that bucket.

Does anyone have any ideas on a reactive Measures Filter that could help? Thanks so much in advance. 

[amitchandak]

@Anonymous , What you are showing is a visual table or your data table?

All measures will respond to the filter of columns in that table and related table unless those have been removed using all or some other option.

The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[Anonymous]

Hi amitchandak,

Thanks! That's the data table. I'm trying to display, in three different Cards;

Wendy's Grand Total ('Her Average')

Average of all Accounts Grand Total ('total average')

Average of just those who match her 'Employee Bucket' category (= a) 1-2) ('peer group total')

I'm able to account for them with;

Card: Grand Total (sliceable) -> Select Wendy - 'her average'

Card: Grand Total (unfiltered - all values) - 'total average'

Card: Grand Total (filtered by all those who match Employee Bucket = 'a) 1-2') - 'peer group average'

Unfortunately, I can't set the third filter at the page or viz level as static, as I want the bit in bold to update to match the Slicer selection (ie I don't always want to select 'Wendy'). So I seem to need a 'LOOKUP -> Filter Measure' based on my slice selection. 

Does that help? (and thank you!)