When averaging over all rows, I would like to use a filter to choose one row's value to change

[Anonymous]

I have a table with names and test score values, I want to change anyone's test score by a percentage change, and recalculate the average score. 

I have created the "new paramater" slicer called value change, and this auto updates with a visual slicer. I then use the following code and it almost works perfctly: 

Measure to calc Ave v2 = 

CALCULATE(
    AVERAGEX(Dummy,If(Dummy[Name]="Sam",'Dummy'[Value]*(1+'Value change'[Value change Value]/100),'Dummy'[Value])),
    ALL('Dummy'[Value],'Dummy'[Name])
)

The All(...) part is important because it means that the average is over all names, and not done name by name. Now, It almost works perfectly because the value for "Sam" is updated and the average changes. However, I would like to use a slicer to choose other names and so I need to edit the If(Dummy[Name]="Sam" part. I tried to use selectedvalue function, but I couldnt get it to work. 

I would appreciate some help. 

[tamerj1]

Hi @Anonymous 
SELECTEDVALUE should work unless you are chosing multiple names at a time or the slicer filter is created. Please share more context perhaps screenshots for better understanding.

[Anonymous]

OK thanks for your reply, if I am missing any details please let me know. So, in the image below yo see a Name slicer showing Kate, you see  atbale that is disconnected from the slicer so it shows all the names, and then you see measure to calc value v1, and v2 (the average version sollow similarly). Now, in V1 which has the following

Measure to calc value v1 = 

CALCULATE(
    SUMX(Dummy,If(Dummy[Name]="Bill" ,'Dummy'[Value]*(1+'Value change'[Value change Value]/100),'Dummy'[Value]))
 )

is working as expected because if the name is Bill then the score is 10% created than the original value but all of the others remain the same, but in V2 where I try and use the seelcted value function to select Kate (which is in the slicer), it is not operating properly because it is extending all by 10%. 

Measure to calc value v2 = 

CALCULATE(
    SUMX(Dummy,If(selectedvalue(Dummy[Name])= Dummy[Name] ,'Dummy'[Value]*(1+'Value change'[Value change Value]/100),'Dummy'[Value]))
 )

I then have the equivalent codes for the average as posted in my original answer but it has the additional All(..) so that it removes all filters and averages over the whole dataset. 

So my issue is, how can I get the selected value function to work so that it only change the name based on the choice I make in the filter. 

[tamerj1]

@Anonymous 
Actually like this

calc Ave v2 =
CALCULATE (
    AVERAGEX (
        'Dummy',
        IF (
            'Dummy'[Name] = SELECTEDVALUE ( SlicerTable[Name] ),
            'Dummy'[Value] * ( 1 + 'Value change'[Value change Value] / 100 ),
            'Dummy'[Value]
        )
    ),
    ALL ( 'Dummy'[Value], 'Dummy'[Name] )
)

[Anonymous]

Thanks for the speedy reply! In this instance by slicerTable[name] do you mean the column name that forms the slicer, because if so that is 'Dummy'[name], which I had already tried and it didn't seem to do anything. Should I create a disconnected table with the same names and make that my slicer?  

[tamerj1]

@Anonymous 
The two tables cannot have the same name. What is the name of the main table?

[Anonymous]

OK, I mmight be getting a little confused because I am trying to use dummy data.

In this instance I have one self contained table called dummy 

and so in this instance I believe the averagex commands are running down all the rows in the name column, but I also want to filter by the name so I put that into a slicer. 

In my main model I will then connect it to a table of names via the Neighbour key, and also a calendar table. 

[Anonymous]

Oh sorry in addition I have the Value change paramater table aswel, which controls the slicer that then edits the scores by x%. 

[tamerj1]

@Anonymous 
Usually the parameter should be in a seperate table. However, try to disable the interactions with the slicer.

[Anonymous]

Sorry I don't think I am being clear in the code below: 

Measure to calc Ave v2 = 

CALCULATE(
    AVERAGEX(Dummy,
    If('Dummy'[Name]= SELECTEDVALUE('Dummy'[Name]),
        'Dummy'[Value]*(1+'Value change'[Value change Value]/100),'Dummy'[Value])),
    ALL('Dummy'[Value],'Dummy'[Name])
)

'Dummy'[name] is the list of student names that have a score, and is somethin I am filtering over (because I want the ability to pick one person to change a score of, but leave the others unchanged), and this is all in one table with the names and scores. 

I also have a value chnage paramater, which has a slicer that varies by x%, and this feeds into the average of the sum via the Value change Value, measure.  

Value change Value = SELECTEDVALUE('Value change'[Value change])

[tamerj1 · Accepted solution]

@Anonymous 

Yes I understand. But notice that you are using a column from the same table as a parsmeter. This does not because pnce you select a nsme the whole table will be filtered down to only the rows that contain that name. The physical relationship that connects the column to its table is stronger than the All function. Create a new table = VALUES (dummy[name] ) and use it as a slicer

[Anonymous]

Thanks so much and thanks for being patient I am new to PowerBI, it worked perfectly!