cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## handling alpha numeric data in a column

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

1 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)

3 REPLIES 3

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
Not applicable

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 %.

@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)