Forum Discussion

yellowold5's avatar
yellowold5
Icon for Helper I rankHelper I
2 years ago

Averagex outcome is not correct using SSAS tabular

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
yellowold5
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      yellowold5
      Icon for Helper I rankHelper I
      2 years ago

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

      Cant use the selectedvalue for ssas tabular:

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.