Forum Discussion

SanderB's avatar
SanderB
Frequent Visitor
7 years ago
Solved

Avererage in column with variant data type

Hi all, I am stuck in the matrix table in PowerBI. See data below. Column VALUE is formatted as numbers, TEXT as text and U_RPT_VALUE also as text. TEST_NUMBER NAME VALUE TEXT U_RPT_VALUE ...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi SanderB 

     

    Try the below

    Measure = 
VAR _text = SELECTEDVALUE( 'Table'[U_RPT_VALUE] )
VAR _search = SEARCH( "<", _text, 1, 0 ) > 0
RETURN IF( _search, _text, AVERAGE( 'Table'[VALUE] ) )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi SanderB 

 

Try the below

Measure = 
VAR _text = SELECTEDVALUE( 'Table'[U_RPT_VALUE] )
VAR _search = SEARCH( "<", _text, 1, 0 ) > 0
RETURN IF( _search, _text, AVERAGE( 'Table'[VALUE] ) )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

  • SanderB's avatar
    SanderB
    Frequent Visitor
    7 years ago

     Hi Mariusz ,

     

    Thank you very much, the use of a measure like that is totally new for me :-)

    It works perfect in the example, but on the complete database I get the "Expressions that yield variant data type" error. 

    This is caused by the Table[U_RPT_VALUE]

    There is a lot of data in this table, dates, <,>,negative numbers,-, text and empty

     

    Is there a way make this usefull? Otherwise I can create a new column and filter all the stuff out and see if it works

     

    • SanderB's avatar
      SanderB
      Frequent Visitor
      7 years ago

      It works! Corrected the column and it is perfect!

      Thank you very much!

  • SanderB's avatar
    SanderB
    Frequent Visitor
    7 years ago

    Hi Mariusz ,

     

    Only one small question about AVERAGE.

    Is it possible to have the decimal places dynamic?

    I thought that the FIXED function will do this, but this does not work.

     

    Measure = 
VAR _text = SELECTEDVALUE( RESULT[Merged] )
VAR _search = SEARCH( "<", _text, 1, 0 ) > 0
RETURN IF( _search, _text, FIXED(AVERAGE(RESULT[VALUE]),(RESULT[PLACES]),1))

    Thanks,

    Sander 

     

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      7 years ago

      HI SanderB 

       

      You need some form of aggregation around RESULT[PLACES], try MIN or MAX like below.

       

      Measure = 
VAR _text = SELECTEDVALUE( RESULT[Merged] )
VAR _search = SEARCH( "<", _text, 1, 0 ) > 0
RETURN IF( _search, _text, FIXED( AVERAGE( RESULT[VALUE] ), MAX( RESULT[PLACES] ), 1 ) )
      Best Regards,
      Mariusz

      Please feel free to connect with me.
      Mariusz Repczynski

       

      • SanderB's avatar
        SanderB
        Frequent Visitor
        7 years ago

        Hi Mariusz,

        I looks like the FIXED function does not work there.

        It will only give zero's and the filters are not working anymore, even without dynamic reference.

        FIXED(AVERAGE( RESULT[VALUE] ),4,1 ))