topic Re: Trouble Calculating a Dynamic Average in DAX Commands and Tips

Trouble Calculating a Dynamic Average

Anonymous — Tue, 03 Dec 2024 19:08:51 GMT

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!

Re: Trouble Calculating a Dynamic Average

Dangar332 — Tue, 03 Dec 2024 20:14:55 GMT

Hi, Anonymous

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.

Re: Trouble Calculating a Dynamic Average

FreemanZ — Tue, 03 Dec 2024 21:04:57 GMT

hi Anonymous ,

try like:

average =

AVERAGEX(

VALUES(data[sub category]),

[KO Profitability Score]

)

Re: Trouble Calculating a Dynamic Average

Anonymous — Wed, 04 Dec 2024 16:36:05 GMT

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

Re: Trouble Calculating a Dynamic Average

Dangar332 — Wed, 04 Dec 2024 17:36:30 GMT

Hi, Anonymous

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.

Re: Trouble Calculating a Dynamic Average

Anonymous — Wed, 04 Dec 2024 20:19:19 GMT

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.

Re: Trouble Calculating a Dynamic Average

Anonymous — Fri, 06 Dec 2024 03:30:57 GMT

Hi Anonymous ,

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.

Re: Trouble Calculating a Dynamic Average

Anonymous — Fri, 06 Dec 2024 14:54:25 GMT

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.

Re: Trouble Calculating a Dynamic Average

FreemanZ — Fri, 06 Dec 2024 15:09:13 GMT

hi Anonymous ,

could you depict your expected result with a table?

Re: Trouble Calculating a Dynamic Average

Anonymous — Mon, 09 Dec 2024 16:48:32 GMT

Hi there! Can you please clarify what you mean?

Re: Trouble Calculating a Dynamic Average

Anonymous — Tue, 10 Dec 2024 07:23:29 GMT

Hi Anonymous ,

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.

Re: Trouble Calculating a Dynamic Average

Anonymous — Tue, 10 Dec 2024 14:36:26 GMT

Hi Neeko,

Please find a sample file here.

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

Thanks!

Maggie

Re: Trouble Calculating a Dynamic Average

Anonymous — Wed, 11 Dec 2024 02:31:16 GMT

Hi Anonymous ,

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.

Re: Trouble Calculating a Dynamic Average

Anonymous — Wed, 11 Dec 2024 14:50:03 GMT

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

Re: Trouble Calculating a Dynamic Average

Anonymous — Wed, 11 Dec 2024 19:01:15 GMT

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!

Re: Trouble Calculating a Dynamic Average

Anonymous — Fri, 13 Dec 2024 13:52:16 GMT

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