Forum Discussion

Specialist707's avatar
Specialist707
Frequent Visitor
3 years ago
Solved

Dynamically Average Selected Field Parameters in Bar Chart

Hi,

 

I'm working on a project where there are numerous inputs, which are then averaged together to come up with a composite score. I would like the ability to be able to select various combinations of measures, and have the selections show in a bar chart, along with the overall composite score which would change based on which inputs are selected.

 

My dataset looks like this:

ProductNameValue
Product 1Input 1 Rating     3
Product 1Input 2 Rating     5
Product 1Input 3 Rating     3
Product 2Input 1 Rating     4
Product 2Input 2 Rating     3
Product 2Input 3 Rating     5
Product 3Input 1 Rating     3
Product 3Input 2 Rating     4
Product 3Input 3 Rating     3
Product 4Input 1 Rating     3
Product 4Input 2 Rating     1
Product 5Input 1 Rating     2
Product 5Input 2 Rating     1
Product 6Input 1 Rating     1
Product 7Input 1 Rating     3
Product 7Input 2 Rating     2
Product 8Input 1 Rating     4
Product 9Input 1 Rating     1
Product 9Input 2 Rating     3
Product 9Input 3 Rating     2
Product 10Input 1 Rating     3
Product 11Input 1 Rating     4
Product 11Input 2 Rating     3
Product 11Input 3 Rating     1

 

I would like a clustered bar chart that has the Product on the y-axis, and the various Inputs, as well as the composite score (average of all inputs) on the x-axis. For Example, for Product 11, there would be 4 bars (3 for the inputs, 1 for the composite score). If I select just input 1&2 in the field parameter, then the composite score would be 3.5 (as well as only show input 1 and 2 in the bar chart , along with a bar for the composite score). If I select all the inputs, then the composite score would be 2.67 (and show 4 bars for that product).

 

I'm struggling to find a solution that would dynamically change the composite score based on the selections, as well as only showing the inputs that are selected. I'm not 100% sold on if field parameters are the way to go, or if there is an alternative way creating a measure for each input, and adding the 4 measures to the x-axis instead of the parameter and having some other kind of slicer.

 

For the parameter, i created 4 measures and selected them in the field parameter options:

Input 1 rating = Calculate(Average([Value]),[Name]="Input 1 Rating")

Input 2 rating = Calculate(Average([Value]),[Name]="Input 2 Rating")

Input 3 rating = Calculate(Average([Value]),[Name]="Input 3 Rating")

Composite Score = Average([value])

 

 

Any help would be greatly appreciated!

DAX

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Specialist707 ,

     

    Please try:

    Composite Score =
AVERAGEX (
    'Parameter',
    SWITCH (
        CALCULATE ( MAX ( 'Parameter'[Parameter] ) ),
        "Input 1 rating", [Input 1 rating],
        "Input 2 rating", [Input 2 rating],
        "Input 3 rating", [Input 3 rating]
    )
)

    Final output:

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.