Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

AVERAGE based on columns added into a parameter field

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for danextian's concern about this issue.

     

    Hi, Anonymous 

    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's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi Anonymous 

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]
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.