Forum Discussion
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 ...
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
Community Champion
Community ChampionHi 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
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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
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.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 ))