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)

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.