AVERAGE based on columns added into a parameter field

[dubro777]

I have a set of columns and want to have another column that averages these for each row.
Now the set of columns are in a parameter field, so I still want to have the option to choose which columns get averaged.

So in the below pic, I want a new column named example Rank. but that changes depending on the parameter I have RSI, RPI and QA score in, so I can choose to remove (1) and keep (2) of them.

[Anonymous · Accepted solution]

Thanks for danextian's concern about this issue.

Hi, @dubro777 

I am glad to help you.

According to your description, first I created three Measure to calculate the average of different fields:

AvgQS = AVERAGE(Parameter[QA Score])

AvgRPI = AVERAGE(Parameter[RPI])

AvgRSI = AVERAGE(Parameter[RSI])

Then created Fields Parameter with the three fields checked:

At this point my parameter table looks like this:

Then I created a Measure to dynamically fetch the selected parameters while calculating the averages:

example Rank = 
VAR _selectedParameter = CONCATENATEX(VALUES(AverageParameter[AverageParameter Fields]), AverageParameter[AverageParameter Fields], ", ")
RETURN
SWITCH (
    _selectedParameter,
    "'Parameter'[QA Score]", [AvgQS],
    "'Parameter'[RPI]", [AvgRPI],
    "'Parameter'[RSI]", [AvgRSI],
    "'Parameter'[QA Score], 'Parameter'[RPI]",([AvgQS]+[AvgRPI]) / 2,
    "'Parameter'[QA Score], 'Parameter'[RSI]",([AvgQS]+[AvgRSI]) / 2,
    "'Parameter'[RPI], 'Parameter'[RPI]",([AvgRPI]+[AvgRSI]) / 2,
    "'Parameter'[QA Score], 'Parameter'[RPI], 'Parameter'[RSI]",([AvgQS]+[AvgRPI]+[AvgRSI]) / 3
)

I have attached the pbix file for this example below, I hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @dubro777 

You can't directly apply an aggregation to field parameters.  They have to be used dimensions or to switch between measures configured in the parameter.  You will need to write an IF or SWITCH formula to switch between different averages based on  the field parameters order column. Applying a conditional formula to either the paramter or parameter fields columns, will result to this error:

The formula would be something like

test =
SWITCH (
    SELECTEDVALUE ( Parameter[Parameter Order] ),
    1, [Avg1],
    2, [Avg2]
)

[dubro777]

Thanks for your support here.

Let me give a bit more info. So the columns within the parameter are measures.

I tried your solution, and will give me the value for each measure in the parameter of only (1) is selected. Which is good, however I need it to Average if more than 1 is selected. So that the overall score changes depending if I am adding more measures. but need it to be dynamic so I can use the parameter slicer to decide which ones.