Averagex outcome is not correct using SSAS tabular

[yellowold5]

Hi everyone, have some issue with my Average outcome, i have a test table:

I have 2 measure, sum 2 and avge 3, the first sum 2 is a simple one= SUM(table(time)-SUM(table(time2) and

the avge 3=Averagex(values(table(bus),sum 2)
the outcome is not correct, its the same as sum 2 but the totals is correct for avge3.

A should be 5.5 and b should be 3.5.
Im using a live connection so selectedvalue is not a options.
thanks for supporting.

second avge 3= A

[Anonymous]

Hi,@yellowold5 I am glad to help you.
Based on your description, the measure you wrote does not return the expected value, calculating the average within each group (the average of all A's and the average of all B's)
You should try this measure:

M_avge 3 = CALCULATE(AVERAGEX('table',[avge 3]),FILTER(ALL('table'),'table'[Bus]=MAX('table'[Bus])))

here is my test data:

sum 2 = SUM('table'[time])-SUM('table'[time 2])

avge 3 = Averagex(values('table'[bus]),[sum 2])

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yellowold5]

Hi I got this error: Syntax or semantic error, reported by analysis services; the function max takes an argument that evalutes to numbers or dates and cannot work with values of type string.

[Anonymous]

Hi,@yellowold5 Thank you for your reply.
I've reused the AS model for live connections and I'm not getting any errors with the above code, you can try using selectedvalue() to make sure that the code doesn't report errors
They both achieve the same result

M_avge 3 = CALCULATE(AVERAGEX('Table_1',[avge 3]),FILTER(ALL('Table_1'),'Table_1'[Bus]=MAX('Table_1'[Bus])))


M_avge 4 = CALCULATE(AVERAGEX('Table_1',[avge 3]),FILTER(ALL('Table_1'),'Table_1'[Bus]=SELECTEDVALUE('Table_1'[Bus])))

M_avge 5 = CALCULATE(AVERAGEX('Table_1',[avge 3]),FILTER(ALL('Table_1'),'Table_1'[Bus]=MIN('Table_1'[Bus])))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[yellowold5]

Hi , thank you for your support, got a error message for max and min:

Cant use the selectedvalue for ssas tabular:

[Anonymous]

Hi,@yellowold5. Thank you for your reply.
I think there should be a problem with your datasource itself, perhaps the version is too old, resulting in a lot of incompatible functions, the max function can not be passed into the text type or some functions can not be used selectedvalue, for example.
I found a similar issue, but unfortunately the final solution was to rely on max/min instead of selectedvalue.
url:Solved: SELECTEDVALUE() function not recognized - Microsoft Fabric Community

Could you try updating the version of the tabular model you are connecting to. Improve compatibility, as for example the selectedvalue() function is available at newer compatibility levels (1200+).
If your data is deployed at an older compatibility level, this may result in most of the basic functions being unavailable, and in fact this will greatly limit your ability to produce reports, as many of the functions will not work or will not support certain data types.
In fact, this will greatly limit your ability to produce reports, as many of the functions will not work or will not support certain data types (e.g., you are experiencing max/min that does not support text).
Whether you can connect to the AS model via the import mode, as this mode of connection to the tabular model has fewer limitations.
But the most important thing is to check if the version of your data source model itself is too old, please try to update the model version to support more common functions in power bi while ensuring data integrity.
Back to your code problem itself, there is no problem with the code itself, I am using these functions to get the value of each row in the current visual to realize the grouping calculation.

The issue at hand is that your data source does not support most of the functions, which is not conducive to creating reports using this model as very many of the functions are not available.
I wish you luck in finding the problem and look forward to your reply.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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