Forum Discussion
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
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
Create a new column like below and that should do the trick. Not sure how you are summarizing values.
New Column = FORMAT(Table[Field],"##.##%")
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 %.
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)