Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculation Group Syntax

I am trying to create calculation items but I cant create them, it only send -1 value, I think I am missing the SELECTEDMEASURE () thing:

 

 

 

Can you show me an example with the following formula:

 

CALCULATE (
    AVERAGE ( 'BV37CC'[CONTRIBUTION COST]),
    'BV37CC'[CONTRIBUTION COST] >= 20000
        && 'BV37CC'[CONTRIBUTION COST] <= 21000)
 

 

3 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    The syntax is wrong. You can't do AVERAGE( [Measure] ). AVERAGE works only with columns of a base table. Please see https://dax.guide/average. Besides, what would that mean? Averaging a measure... over what? To average something you have to indicate what you're averaging over and your formula does not state it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I am trying to perform a calculation group based in the following tables:

      I am using multiple formulas for the table in the right and I need all those table together in a single one that why I am using calculation groups, for the tables on the right I am using the following formula:

       

      Num rows 20-21k =
      CALCULATE (
          COUNTROWS ( 'BV37CC' ),
          'BV37CC'[CONTRIBUTION COST] >= 20000
              && 'BV37CC'[CONTRIBUTION COST] <= 21000)
       
      Avg 20-21k =
      CALCULATE (
          AVERAGE ( 'BV37CC'[CONTRIBUTION COST]),
          'BV37CC'[CONTRIBUTION COST] >= 20000
              && 'BV37CC'[CONTRIBUTION COST] <= 21000
      )
       
      Both calculations are to get the count and avg of numbers of the table on the left,
       
      In calculation group I am creating a calculation item with the following formula:

       

      CALCULATE ( SELECTEDMEASURE (), 'BV37CC'[CONTRIBUTION COST] >= 20000 && 'BV37CC'[CONTRIBUTION COST] <= 21000 )

      After putting the formula I tried to create the calculation group in a table in power bi with the Matrix but I think I am missing something:

       

      I need a single table with all the multiple calculations from the right tables,

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Anonymous,

    The average function should not be directly nested with selectedmeasure functions. I'd like to suggest you create a variable with summarize function to apply the first aggregated, then you can use the iterator function to apply the second aggregated.

    Measure Totals, The Final Word 

    In addition, you can also take a look at the following links about calculated groups if helps:

    Calculation Groups - SQLBI

    Using calculation groups to selectively replace measures in DAX expressions - SQLBI

    Regaerds,

    Xiaoxin Sheng