Forum Discussion

Zbig62's avatar
Zbig62
Regular Visitor
2 years ago
Solved

problem with calculating the average using variables defining a range of values

Hello I am a beginner DAX user and I have a serious problem with calculating the average using variables defining a range of values. The problem is as follows: Table: 'public Measurement' 1. aver...
  • Zbig62's avatar
    Zbig62
    2 years ago

    welcome back
    Thank you for your suggestions, it allowed me to locate the problem.
    Instead of count() I used the countx() function and it works very well
    thank you again

     

    var stop_av = CALCULATE(
        COUNTX('public Measurement';'public Measurement'[number]);
        FILTER(
            ALLSELECTED('public Measurement');
            'public Measurement'[characteristicId] = MAX('public Measurement'[characteristicId])
        )
    )
tamerj1's avatar
tamerj1
Community Champion
2 years ago

Hi Zbig62 

as part of the debugging process you can create a measure Test = count('public measurement'[number]) and see if returns what you've expected it to return. 

  • Zbig62's avatar
    Zbig62
    Regular Visitor
    2 years ago

    Hello
    Thanks for fast reaction.

     

    This is where I have a problem, see the results in the table where the stop_av variable should have the value 10.
    As a result, it obtains a bad average value
    Please help me, I don't know how to do it

     

     

    AV =
    var stop_av = count('public Measurement'[number])
    var  start_av = 1
    var av =
    CALCULATE (
         AVERAGEX('public Measurement';  'public Measurement'[result]);
       FILTER (
            ALLSELECTED ('public Measurement');
            'public Measurement'[characteristicId] = MAX ( 'public Measurement'[characteristicId])
        );
            'public Measurement'[number] >= start_av ; 'public Measurement'[number] <= stop_av
    )
    RETURN
    av
     

     

     
    • tamerj1's avatar
      tamerj1
      Community Champion
      2 years ago

      Zbig62 

      Thank you for clarifying your problem one more time. That was clear to me already. My point was that it doesn't seem that you are aware of what var stop_av = count('public Measurement'[number]) returns. You only have an expectation that it should always return 10 while this is not true. In fact this expression will be evaluated within the filter context of evaluation. It n other words it might return different number for each number-characteristicid combination depending how many times this combination is repeated in the Source data table which I personally don't know for lack of information. But I can say it won't be 10 for all combinations unless all combinations have actually 10 rows. If you want to return the maximum [number] for each [caharastaristid] then you can try

      var stop_av = CALCULATE ( MAX ( 'public Measurement'[number] ), FILTER (  ALLSELECTED ( 'public Measurement', 

              'public Measurement'[characteristicId]= MAX ( 'public Measurement'[characteristicId])
          )
       
      However, that might not be it as you might face issues with AUTOEXIST. Along with ALLSELECTED, AUTOEXIST might produce problems given that [Result] column is a value column that is part of the filter context. 
      If you can provide a sample file, it would be much easier to propose a totally different solution perhaps utilizing a NO CALCULATE approach that proofs to he much more reliably in such scenarios.