Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

TypeNameMonthNumeratorDenominatorResult
1.1AApr-180.2120.00%
1.1DApr-18187811878
2.2BMay-189910099.00%
2.2FJun-1832 ABC/ FTE/Day 32 ABC/ FTE/Day
3.3CJun-1818118
3.3EMay-1828657.00 28657

  Required out put

  AverageAverageAverage 
TypeNameApr-18May-18Jun-18 
1.1A20.00%   
1.1D1878   
2.2B 99.00%  
2.2F  32 ABC/ FTE/Day
3.3C  18 
3.3E 28657  

actual Matrix Visualactual 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)

View solution in original post

3 REPLIES 3
S184019
Advocate III
Advocate III

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

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)

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.