Show only Asset Numbers that are below a certain measure value

[th1997]

Hello All,

I have a dataset that contains an Asset Number, a date of win column, and a numeric win value column.
I created a simple measure called 85% of Average, calculated as such: 85% of Average = AVERAGE(Table[Win])*.85

This average value will change based on slicers and dates that users of the dashboard will select, and this needs to be the case. I don't think I can create an Average Column in the dataset because the number of observations will change based on the date selection, slicer selection, etc. 

My goal is the following: I only want to show Asset Numbers that are under the 85% Average value on both a bar chart and a table. 

I tried to use the following measure to filter out the asset numbers that are below the 85% average number, but it only selects values below 0: Below85Percent = IF( AVERAGE(Table[Win]) < [85% of Average Net Win], AVERAGE(Table[Win]), BLANK()). This is incorrect because an individual Asset Number's average can be a positive number and still be less than the overall :

I also tried to add "85% of Average" to a slicer and as a page filter, but wasn't able to do so.

This is an example of what I'd hope to achieve, both in this format and a tabular format: A bar graph and a table that will only show the asset numbers that have an average win values less than the overall "85% of Average" win value, which in this case would be all the values less than $465.84 

[CoreyP · Accepted solution]

Your average measure should be this:

85 % AVG = CALCULATE( AVERAGE( AssetWins[Win Value] ) * .85 , ALL( AssetWins[Asset Number] ) )

Then you can create a filter flag measure like this:

Less Than 85% AVG = IF( SUM( AssetWins[Win Value] ) < [85 % AVG] , 1 , 0 )

Then apply that measure as a filter on the visual where the value equals 1. 

[CoreyP]

Ohhhh, but now I see the problem. Let's say you've got all your asset's there and the 85% AVG is 100. You have your flag filter to say "Only show assets below the 85% AVG of 100." When that filter is applied, it reduces the number of assets, and thus changes the 85% AVG of 100 to something else, which then changes which assets should be filtered and doesn't display the correct assets. 

Interesting...

[CoreyP]

Sorry, I misread your post. If you want the 85% AVG amount to change depending on slicer context, change the ALL to ALLSELECTED.

[CoreyP · Accepted solution]

Your average measure should be this:

85 % AVG = CALCULATE( AVERAGE( AssetWins[Win Value] ) * .85 , ALL( AssetWins[Asset Number] ) )

Then you can create a filter flag measure like this:

Less Than 85% AVG = IF( SUM( AssetWins[Win Value] ) < [85 % AVG] , 1 , 0 )

Then apply that measure as a filter on the visual where the value equals 1. 

[th1997 · Accepted solution]

This was actually the correct answer - I made the measure:

85% of Average =

CALCULATE(AVERAGE(Table[Win]),ALL(Table[Asset Number]))*.85

And then had to create a second measure to use on the asis of my graph/table, which was:

Below85Percent = IF( AVERAGE(Table[Win]) < [85% of Average], AVERAGE(Table[Win]), BLANK())