Dynamically Average Selected Field Parameters in Bar Chart

Specialist707 — Tue, 16 May 2023 22:46:39 GMT

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:

Product	Name	Value
Product 1	Input 1 Rating	3
Product 1	Input 2 Rating	5
Product 1	Input 3 Rating	3
Product 2	Input 1 Rating	4
Product 2	Input 2 Rating	3
Product 2	Input 3 Rating	5
Product 3	Input 1 Rating	3
Product 3	Input 2 Rating	4
Product 3	Input 3 Rating	3
Product 4	Input 1 Rating	3
Product 4	Input 2 Rating	1
Product 5	Input 1 Rating	2
Product 5	Input 2 Rating	1
Product 6	Input 1 Rating	1
Product 7	Input 1 Rating	3
Product 7	Input 2 Rating	2
Product 8	Input 1 Rating	4
Product 9	Input 1 Rating	1
Product 9	Input 2 Rating	3
Product 9	Input 3 Rating	2
Product 10	Input 1 Rating	3
Product 11	Input 1 Rating	4
Product 11	Input 2 Rating	3
Product 11	Input 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!

Re: Dynamically Average Selected Field Parameters in Bar Chart

v-jianboli-msft — Thu, 18 May 2023 08:38:14 GMT

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.

topic Re: Dynamically Average Selected Field Parameters in Bar Chart in DAX Commands and Tips

Dynamically Average Selected Field Parameters in Bar Chart

Re: Dynamically Average Selected Field Parameters in Bar Chart