handling alpha numeric data in a column

[Anonymous]

Hi All,

Would like have if average of % values as % and average of numbers as numbers in a matrix table, I have created matrix visula but in vain the % values are not as %  but avg of numbers are perfect.

My sample table

Type	Name	Month	Numerator	Denominator	Result
1.1	A	Apr-18	0.2	1	20.00%
1.1	D	Apr-18	1878	1	1878
2.2	B	May-18	99	100	99.00%
2.2	F	Jun-18	32 ABC/ FTE/Day		32 ABC/ FTE/Day
3.3	C	Jun-18	18	1	18
3.3	E	May-18	28657.00		28657

  Required out put

		Average	Average	Average
Type	Name	Apr-18	May-18	Jun-18
1.1	A	20.00%
1.1	D	1878
2.2	B		99.00%
2.2	F			32 ABC/ FTE/Day
3.3	C			18
3.3	E		28657

actual Matrix Visual

You can see the actual Matrix table where the % target row values are not showing as %.

My Reulst field is Decimal Number format to handle %, Number & Text.

can any one help me on this.

Murali

[S184019]

Create a new column like below and that should do the trick.  Not sure how you are summarizing values.

New Column = FORMAT(Table[Field],"##.##%")

[Anonymous]

Hi, Thanks for your reply.

This format will apply also if the column has numbers without %. In my result column for example the % values as 0.14, 0.95... and also with numbers example 12, 250...

I want to bring all these numebers as average in Matric table with if the numbers are % then the avg Number should show with %a and other wihout %.

[S184019 · Accepted solution]

@Anonymous

Hey, 

Perhaps formatting could occur with a minor modification to the formula like this: 

if('Table'[Field] <=1, FORMAT('Table'[Field],"##.##%"), FORMAT('Table'[Field],"General Number"))

This is also true if you wanted to use an if (conditions to average), summarize table, (conditions not to summarize)