Trouble Calculating a Dynamic Average

[mbmcdonald2]

Hi All,

I am trying to add a new measure that dynamically calculates the average of all the values based on the parameter selection and I am getting an error saying "parameter is not the correct type". I would like this new column to spit out the 17 that is highlighted in the screenshot below. 

Any help is greatly appreciated.

thank you!

[v-tangjie-msft]

Hi @mbmcdonald2 ,

It works fine through my tests. Can you provide sample pbix files (taking care to protect private data) for us to test?

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

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

[mbmcdonald2]

Hi Neeko,

Please find a sample file here. 

Please let me know if you have any troubles with access.

Thanks!

Maggie

[v-tangjie-msft · Accepted solution]

Hi @mbmcdonald2 ,

Create this measure.

Average Score = 
CALCULATE ([Score], ALLSELECTED('OST'))

Best Regards,

Neeko Tang

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

[mbmcdonald2]

Hi there,

Thank you so much for the help on this. It appears that while that solution worked for that particular instance, I am having trouble applying correctly to another instance. As you see in the screenshot, the "total" is showing as 19, however, that is incorrect as the average should be closer to 9. Do you have any idea why this might be?

Appreciate any assistance here in advance. Thank you so much!

[mbmcdonald2]

Hi @v-tangjie-msft - just following up on the above. If you have any insight into this it would be greatly appreciated. Thank you!

[mbmcdonald2]

This worked! Thank you so much everyone for all of the assistance!

[v-tangjie-msft]

Hi @mbmcdonald2 ,

Thanks to Dangar332  and FreemanZ  for their quick replies. I have some other thoughts to add:

Based on your description, the Score you are displaying on the visual object is the measure, correct? If I have misunderstood you please clarify in a follow up reply.
Then we can create a new measure.

Avg = CALCULATE([Score],ALLSELECTED('Table'))

Then the result is as follows.

Best Regards,

Neeko Tang

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

[mbmcdonald2]

Hi All,

Thanks so much for your continuous help here. 

The one challenge here is that the "sub-category" field comes from a parameter, which I believe may require some differences in the formula. 

You'll see when I create the formula per your suggestion: 

Average Score =

CALCULATE ([Score], ALLSELECTED('Field Select Parameter'))

I get the same figures as my existing column.

[FreemanZ]

hi @mbmcdonald2 ,

could you depict your expected result with a table?

[mbmcdonald2]

Hi there! Can you please clarify what you mean?

[FreemanZ]

hi @mbmcdonald2 ,

try like:

average =

AVERAGEX(

    VALUES(data[sub category]),

    [KO Profitability Score]

)

[mbmcdonald2]

Hi there,

Thanks so much for getting back to me. So when I update per your suggestion above I just get the same values, not the '17' value I was expecting. The first column is based on a parameter and changes between category and sub-category. 

Average KO Profitability Score = averagex (VALUES('Field Select Parameter'[Selection Parameter Fields]),

[KO Profitability Score])

[Dangar332]

Hi, @mbmcdonald2 

Try below amended measure 

Average KO Profitability Score = 
averagex (
 allselected(
      'Field Select Parameter'[Selection Parameter Fields]),
       [KO Profitability Score]
)

Best Regards,
Dangar

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

[mbmcdonald2]

Hi there,

Thanks so much for your help. The solution is now showing up like this (it should show up as all 17s) using the above formula.

[Dangar332]

Hi, @mbmcdonald2 

You had used a measure as Average argument where it's only accept Column refrence not Measure, try to put score column that shown in screenshot .

Best Regards,
Dangar

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