Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Displaying % as Ratio

Hi All, 

 

I am trying to make a % visible in a different format. 

E.g. Instead of 1.81% I want to show 1:50.

 

In Excel I use the following formula: "="1" & ":" & ROUND(($B$1/C1),0)"

In Power BI I am able to calculate the % via a measure but how can I make sure it displays in the 1:50 format?

 

With kind regards,

Mike

1 ACCEPTED SOLUTION

Hi @Anonymous

 

No worries, please see below the proposed solution.


Here is the measure that I created

Column E Result = "1:" & ROUND(CALCULATE(SUM('Table1'[A])/SUM('Table1'[B])),0)

And this is what the result looks like

 

Power BI - DELETE.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

9 REPLIES 9
GilbertQ
Super User
Super User

Hi @Anonymous

 

Could you provide a sample of data, one where it would calculate the % and one where it would be the 1:xx (Or even if the values are the same, what the sample data is)?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi there!

 

Thanks for your response please see below:

In the Excel formula its ROUND(B/A,0)

 

A       B      C      D     E
80000   2000   0.025  40    1:40
3000    20     0.006  150   1:150
400     5      0.0125 80    1:80

 

I get to calculate column C as well as the correct result for behind the : now how do I format it so that it looks like 1:##?

Ratio = FORMAT(ROUND(divide(sum(B),sum(A),0),"1:")

 

Bests,

Mike

Hi @Anonymous

 

No worries, please see below the proposed solution.


Here is the measure that I created

Column E Result = "1:" & ROUND(CALCULATE(SUM('Table1'[A])/SUM('Table1'[B])),0)

And this is what the result looks like

 

Power BI - DELETE.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

very useful,thanks

Hi  @GilbertQ  , Can you please assist me in my scenario.

 

I have created a ratio and am trying to place it in MATRIX visual as have to display data in following manner -

 

Consultant

Total Leads submitted

Count of Approved Leads but no orders

Leads not approved

Count of Approved Leads and orders Placed

Total Leads : Approved Leads and orders Placed

A

47

2

35

10

=Round(47/10,0)   &":1"

= 5 : 1

B

20

5

5

10

2 : 1

 

When I am trying to do the same in matrix visual it is not happening. When I place ratio without the columns with count then it is working - image below is as per data of Consultant A above

 

ratio_capture.PNG

 

 

Also, another query is, since it is creating ratio in text format, I wish to sort the ratios with lowest ratio on top to highest ratio.

Example: instead of 2:1, 37:1 then 5:1 wish to display information as 37:1, 5:1 then 2:1

 

Please let me know.

 

Thanks.

Regards,

Ashish

 

 

 

 

 

 

 

 

 

Hi @Ashish_kumar12 

 

THis measure will work for you below.

 

ROUNDUP(DIVIDE(SUM('Table'[Total Leads]),SUM('Table'[Count aprroved])),0) & ":1"

 

And here is the output

 

image.png 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Awesome that workes! Thanks!

Perfect, glad it solved your issue.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Could you help me with my scenerio? 

I created a Matrix in PBI and I want to calucate the ratio between 2023 with 2022 and another column for 2023 with 2021.

 

choiyn98_0-1682821701901.png

choiyn98_1-1682821823592.png

 

The result I want is to show % change between 2023 with 2022 and another column for 2023 with 2021.

 

% Change with last year

-8%

-13%

5%

-48%

24%

-25%

14%

56%

-11%

-5%

% Change with 2 years

-25%

-11%

88%

-36%

20%

-15%

-31%

21%

97%

4%

 

 

 

Thanks in advance!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.